SOQL to retrieve all contacts for an Account (related and direct)

  • I have an account with 4 contacts, two are direct and two are indirect contacts. I'm trying to retrieve all 4 contacts in an SOQL query but can't seem to get it to work .

    For example if I run this query in the developer console I get back all 4 records and it shows the same Account.Id for all records (note i'm using my developer account that only has a single Account and the 4 contacts for these tests.

    SELECT Id, Name, Email, Account.Id FROM Contact
    

    But if I ask for the Contacts by specifying the Account

    SELECT Id, Name FROM Contact where Account.Id = '<SOME_VALID_ID>'
    

    I only get the two direct contacts . Similarly when I run this query to get all the contacts for an account I also only ever get the two direct contacts back.

    SELECT Id, Name, (SELECT AccountId, Name, Email, HomePhone  FROM Contacts where AccountId = '<SOME_VALID_ACC_ID'>) FROM Account WHERE Id = '<SOME_VALID_ACC_ID'>
    

    This query also only returns true if don't constrain the inner SELECT on AccountId. If anyone could let me know if this is possible and how to do it I'd greatly appreciate it.

    What do you want to get ? The first query is giving you all contacts

    Are you using the Salesforce functionality that allows you to relate a single contact to multiple accounts? Your usage of 'Direct' and 'Related' contacts makes me think this, but it would be good to confirm.

    yeah sorry @sfdcFanBoy - this is in my personal devloper account and there's only a total of 4 contacts in the system. I'd like to get the 4 contacts that are associated with the Account.Id in question. That doesn't seem possible

    @DerekF - yes i'm using the new (I think summer 2016?) feature of relating a single contact to multiple accounts. I'm super new to SalesForce so hopefully I'm not using the wrong terminology here. What's puzzling is that the query works fine in the developer console but not when I run it in Apex code.

    Updated original question to use SF terminology

    Did you update your Apex Class to version 37.0 (Summer 16), if not it won't handle the new feature correctly.

  • Derek F

    Derek F Correct answer

    5 years ago

    After clarifying in a comment on your question, we're working with the Associate Contacts with Multiple Accounts feature, which was made generally available in Summer '16 (API v37.0)

    After you've enabled Contacts to be related to multiple Accounts, if you wish to retrieve all the related contacts on an Account, you'll need to query the AccountContactRelation object, which is the junction object Salesforce is using to track the Many-to-Many relationship.

    The following queries should work

    Using a semi-join:

    SELECT Id, Name FROM Contact WHERE Id IN (SELECT ContactId FROM AccountContactRelation WHERE AccountId = <account Id here>)

    Using a parent-child subquery

    SELECT Id, (SELECT Contact.Name FROM AccountContactRelations) FROM Account WHERE Id = <account Id here>)

    The different results that you're getting between the dev console and compiled Apex may be due to your Apex using an API version of 36.0 or below.

    As an aside, anyone know a good way to figure out what the API name of a standard sObject is? I ended up needing to go into the workbench to find the object API name for this newly introduced object because the object's label in the web interface (Account Contact Relationships) is not the same as the API name.

    You could loop through the global describe and match on the label. `for (SObjectType s : Schema.getGlobalDescribe().values()) { if ('Account Contact Relationships'.equals(s.getDescribe().getLabel())) system.debug(s); }`.

    Do these approaches include the `Contact` records that are "directly" related (via `AccountId`, I assume)?

    Thanks @DerekF! Worked perfectly. I didn't realize there was a junction object in there. I need to do some more reading up on this apparently. Thanks again!

    @AdrianLarson `Direct` is a standard field of the new `AccountContactRelation` object. I believe that a given Contact can only be directly related to one Account. From what I've seen in my company's org, selecting the option to allow Contacts to be related to multiple Accounts wipes out the `AccountId` of existing contacts (and uses that to determine which record in `AccountContactRelation` should have `Direct` set to true).

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used