8

I want to get the account child (contact) and contact child.

account
    contact
        contact 
              contact etc.,

I HAVE TRIED FOLLOwING CODE:

List<accont> acc = [select id,name, (select id,(select id,firstname from ChildContacts__r),firstname from contacts) from account id = "qweqwr243rsds"];

How to get the value nested by using for loop ?

Please guide me.

Flow Diagram

Account(top of the tree)
|  |
|  |__ Contact_1
|  |      |
|  |      |__ > Child Contact 1
|  |
|  |                   |
|  |                   |__ > Child Contact 1
|  |
|  |_______________________> Contact_2
|  |
|  |_______________________> Contact_3
|
|
|_____________________________________
                                      |
                                      |
Contact_4   _______________________> Contact_3

**(but Contact4 is not related with account) Contact 3 only related with account**


**I need the hierarchy structure of**  



Account=>Contact_1>=>Child_Contact_1>=>Child_Contact_1>=>Contact_1
Contact_2 and Contact_3

NO NEED CONTACT 4

4 Answers 4

15

Unfortunately this is not possible in a single query. You are trying to traverse multiple levels when moving from Parent-Child. You can only traverse one level when dealing with Parent to Child. When dealing with Child to Parent, you can traverse up to 5 levels.

From the Documentation

  • In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example,
    Contact.Account.Owner.FirstName (three levels).
  • In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM
    clause specifies Account, the SELECT clause can only specify the
    Contact or other objects at that level. It could not specify a child
    object of Contact.

http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_relationships.htm

To get what you are looking to do you will need to use multiple queries and leverage maps and sets to try to collect grandchildren and great-grandchildren records.

3

You may not be able to get that in one query. You can try something like this ( code may contain some errors but will give you an idea ).

Map<id,account> acc = new Map<id,Account>([select id,name, (select id,firstname from Contacts) from account where criteria__c='x']);

Map<id,Contact> contactMap = new Map<id,Contact>();

for(Account tempAccount:acc.values()){

  for(Contact con:acc.Contacts){
    contactMap.put(con.id,con);
  }
}

List<ChildContacts__c> chMap = [select id,firstname__c from childContact where contactLookup__c in contactMap.keySet()];
3
  • You are the rock : I have doubt : ChildContacts__c is the one of the lookup field of the contact and related itself of the contact. so what is the purpose of the last line. it will contain nested contact list?
    – Alex
    Commented May 23, 2014 at 18:04
  • I am not clear about the relationship that you want to show. My example is for a 3 level object relationship. Depending on what you want to do with the list you would have to use the Account and Contact map to get the relationship. For example from ChildContact__c ch1, you can get the corresponding Contact con= contactMap.get(ch1.contactLookup__c). And the corresponding account = acc.get(con.AccountId).
    – AslamK
    Commented May 23, 2014 at 22:49
  • I want to get the n level relationship of the contact. for construct the JSON string.
    – Alex
    Commented May 24, 2014 at 5:15
3

If you are able to "tie" all of those Contacts up to the main Account, you can query for all of them, and then organize them together. This should be possible with one query on Contacts, in addition to any query you may need on Account.

First, create a custom field on Contact that is either a formula or populated by an Apex Trigger, which identifies which Account is at the top of the hierarchy. There are good reasons why this should be a trigger, but you may be able to get away with a formula if you don't have too many records, or too tall a contact hierarchy. For example, a formula field supporting 5 levels of hierarchy Account_Id__c might look like this:

IF(
    ISBLANK(Parent_Contact__c),
    AccountId,
    IF(
        ISBLANK(Parent_Contact__r.Parent_Contact__c),
        Parent_Contact__r.AccountId,
        IF(
            ISBLANK(Parent_Contact__r.Parent_Contact__r.Parent_Contact__c),
            Parent_Contact__r.Parent_Contact__r.AccountId,
            IF(
                ISBLANK(Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.Parent_Contact__c),
                (Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.AccountId,
                IF(
                    ISBLANK(Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.Parent_Contact__c),
                    Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.Parent_Contact__r.AccountId,
                    ""
                    )
                )
            )
        )
    )
)

Then your query will look like

list<Contact> allContacts = [select Id, Parent_Contact__c from Contact where Account_Id__c in :setOfAccountIds];
map<Id, Contact> conId2ConMap = new map<Id, Contact>(allContacts);

You can then parse through these contacts and build your hierarchy:

map<Id, list<Contact>> conId2childrenMap = new map<Id, list<Contact>>();
for (Contact con : allContacts)
{
    list<Contact> templist;
    if (conId2childrenMap.containsKey(con.Parent_Contact__c))
        templist = conId2childrenMap.get(con.Parent_Contact__c);
    else
        templist = new list<Contact>();

    conId2childrenMap.put(con.Parent_Contact__c, templist);
}

The map conId2childrenMap is a map of parent contact Id to list of child contacts, and can be used to recursively build your JSON.

5
  • Thanks for your solution and its giving the parent of the contact but its not related with account I ahve modify my question could you please look at now. you are closing my solution pls help.
    – Alex
    Commented May 24, 2014 at 18:58
  • I don't understand what you are missing. As far as I can tell, the map of parent contact to children contacts should cover your use case. Is there a case that is missing? Commented May 24, 2014 at 21:05
  • Yes, conId2childrenMap giving the Contact_4 value only but i need Child_Contact_1>=>Child_Contact_1>=>Contact_1 Contact_2 and Contact_3. I think we need to change this condition if (conId2childrenMap.containsKey(con.Parent_Contact__c))
    – Alex
    Commented May 24, 2014 at 21:35
  • I want to get the nested child list(top to bottom level). Account and find the child contact(Pcon) of that account. after the find the child contact of "Pcon" contact. etc.,
    – Alex
    Commented May 24, 2014 at 21:38
  • It still sounds like my answer will include all of the contacts you want to retrieve, as long as you also create the formula field as described. Commented May 27, 2014 at 11:36
1

If you're specifically looking to obtain the 3 level deep (or however many levels deep) data structure, then you could query the multiple levels independently and then wire them together using the JSON.serialize and JSON.deserialize methods.

Here's an example for how to wire a List of Contacts to an Account:

// given:
//   List<Contact> contacts
//   Account acct

String accountJson = JSON.serialize(acct);

String contactsJson = '"Contacts" : {';
contactsJson += '"totalSize" : ' + contacts.size() + ',';
contactsJson += '"done" : true,';
contactsJson += '"records" : ' + JSON.serialize(contacts);
contactsJson += '}';

accountJson = accountJson.substring(0, accountJson.length()-1) + ',' + contactsJson + '}';
Account accountWithContacts = (Account) JSON.deserialize(accountJson, Account.class);

Going multiple levels deep, as in your case, would require a little more footwork, but that's the general concept. Note that depending on how much data and how many levels deep you're going this can be pretty expensive from a CPU time standpoint, and you might run into governor limits.

I wrote a blog post about this (includes a reusable utility to do the JSON wiring for you) that I can share if requested.

2
  • Can you please add some detail? As it stands, this post looks like it exists purely to promote traffic to your blog, which is not how SFSE is supposed to work. For reference, please visit the help center and especially How to Answer.
    – Adrian Larson
    Commented Aug 12, 2016 at 14:56
  • agreed - updated my answer Commented Aug 12, 2016 at 18:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .