SOQL statement across Account, Contact and AccountContactRelation in SF

Question

I am trying to write a SOQL statement to fetch data from three objects Account, Contact and AccountContactRelation.

I am able to get data : 1 Account with array of 1..N Contacts from this query :

SELECT 
Account.Name, 
Account.Id, 
(SELECT Contact.Id, Contact.FirstName, Contact.LastName FROM Account.Contacts), 
FROM Account 
WHERE Account.Id = 'XXXX'

Table AccountContactRelation has three fields that I need to include to Contact query. When I tried to embed a additional query into the existing one, I am getting an error Unknown error parsing query, the query below.

SELECT 
Account.Name, 
Account.Id, 
(SELECT Contact.Id, Contact.FirstName, Contact.LastName, (SELECT AccountContactRelation.ContactId, AccountContactRelation.IsDirect FROM AccountContactRelations) FROM Account.Contacts), 
FROM Account 
WHERE Account.Id = 'XXXX'

When I build the query in another way I am getting the data,

SELECT+Account.Name,+Account.Id,+(SELECT+Contact.Id,+Contact.FirstName,+Contact.LastName+FROM+Account.Contacts),+(SELECT+AccountContactRelation.ContactId, AccountContactRelation.IsDirect, AccountContactRelation.LastModifiedDate+FROM+AccountContactRelations)+FROM+Account+WHERE+Account.Id+=+'XXXX'

but the result is 1 Account with array of 1..N Contacts, and second array of 1..N AccountContactRelations.

{
    "totalSize": 1,
    "done": true,
    "records": [
        {

            "Name": " test x",
            "Id": "vKM3QAN",
            "Contacts": {
                "totalSize": 2,
                "done": true,
                "records": [
                    {

                        "Id": "QnQAI",
                        "FirstName": "aaa",
                        "LastName": "bbb"
                    },
                    {

                        "Id": "9QAC",
                        "FirstName": "ccc",
                        "LastName": "ddd"
                    }
                ]
            },
            "AccountContactRelations": {
                "totalSize": 2,
                "done": true,
                "records": [
                    {
                        "ContactId": "QnQAI",
                        "IsDirect": true,
                        "LastModifiedDate": "2023-09-13T07:40:54"
                    },
                    {

                        "ContactId": "9QAC",
                        "IsDirect": false,
                        "LastModifiedDate": "2023-11-03T14:08:02"
                    }
                ]
            }
        }
    ]
}

I need to include the fields like AccountContactRelation.IsDirect and others into the first array, like below :

{
    "totalSize": 1,
    "done": true,
    "records": [
        {

            "Name": " test x",
            "Id": "vKM3QAN",
            "Contacts": {
                "totalSize": 2,
                "done": true,
                "records": [
                    {

                        "Id": "QnQAI",
                        "FirstName": "aaa",
                        "LastName": "bbb",
                        "IsDirect": true,
                        "LastModifiedDate": "2023-09-13T07:40:54"
                    },
                    {

                        "Id": "9QAC",
                        "FirstName": "ccc",
                        "LastName": "ddd",
                        "IsDirect": true,
                        "LastModifiedDate": "2023-11-03T14:08:02"
                    }
                ]
            }
        }
    ]
}

Any idea how to do that. Thank you very much!

Answer 1

Nice question! You have few options here, depends what you expect as end result, what format is closest to your needs.

This is pretty straightforward - you go "down" the related list once to pull relations and then "up" to the contact. You'll get a mixed bag of direct and indirect. I think that's what you're after.

SELECT Id, Name,
    (SELECT Contact.FirstName, Contact.LastName, IsDirect FROM AccountContactRelations)
FROM Account

This one is bit more exciting. 2 related lists, one with direct contacts, one with indirects:

SELECT Id, Name,
    (SELECT FirstName, LastName FROM Contacts),
    (SELECT Contact.FirstName, Contact.LastName FROM AccountContactRelations WHERE IsDirect = false)
FROM Account

Lastly (only via API, not available in Apex yet) you could nest the related lists in the query. Something like Account -> Contacts -> their relations to other accounts. No idea if it'd be of any use to you.