[SalesForce] Display Parent, child and grandchild’s records

Once a parent(Contact) is selected I have to display a list of related records from child, grandchild and grandchild's child in one single page block. All the objects are in a lookup relationship with their parent. The query which I'm writing is not getting saved and giving the error:
"Didn't understand relationship 'Grandchild__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name."
Please help me with apex, Wrapper class and VF code as well.
Parent- Contact
Child- Child__c
Grandchild- Grandchild__c
Grandchild's child- GreatGrandChild__c
Below is my code.

Controller- 

public with sharing class MyController {    
Public String name {get;set;}
Public String newContactName{get;set;}
Public Contact objContact{get;set;}
Public List<Child__c> cmList{get;set;}
Public List<Wrapper> wrapList{get;set;}
public Boolean flag{get;set;}
Public MyController(){
    flag =  true;
  objContact = new Contact(); 
}  

Public PageReference doSave(){
    try{
        insert objContact;
    }catch(Exception e){
        System.Debug('In catch '+e.getMessage());
    }
    return null;
} 
public List<SelectOption> getContactList(){
    List<SelectOption> contactList = new List<SelectOption>();
    contactList.add(new SelectOption('-----Select----','-----Select----')); 
    List<Contact> conList = [select id , Name from Contact];
    for(Contact c :conList){
        contactList.add(new SelectOption(c.Id,c.Name));     
    }
   return  contactList;    
}

Public void showrecord(){
    wrapList = new List<Wrapper>();
    system.debug('inmethod');
    cmList = [Select id, Name, Contact__c, FieldA__c, (Select id, Name, Child__c, FieldB__c (Select id, Name, GrandChild__c, FieldC__c from GreatGrandChild__r) from GrandChild__r) from Child__c where Contact__c =: name];
    for(Child__c cm :cmList)
    {
        wrapList.add(new Wrapper(false,cm));
    }        
    system.debug('inmethod'+ cmList);
}
Public Class Wrapper{
    Public Boolean ischeck{get;set;}
    Public Child__c NCM{get;set;}
    Public Wrapper (Boolean ischeck, Child__c NCM){
        This.ischeck=ischeck;
        This.NCM=NCM;
    }    
}
}

Page- 

     <apex:page controller="MyController">
    <apex:form >
    <apex:pageBlock >    
    <apex:pageBlockSection title="Contacts" rendered="{!flag}">
            <apex:outputLabel value="Name"/>
            <apex:selectlist value="{!name}" size="1" id="values"> 
                <apex:SelectOptions value="{!ContactList}"/>  
                <apex:actionSupport event="onchange" action="{!showrecord}" reRender="con"/>          
            </apex:selectlist>
            <apex:pageBlockTable value="{!wrapList}" var="v" id="con">
                <apex:column >
                    <apex:inputCheckbox value="{!v.ischeck}"/>
                </apex:column>
                <apex:column value="{!v.NCM.name}"/>
                <apex:column value="{!v.NCM.Contact__c}"/>
                <apex:column value="{!v.NCM.FieldA__c}"/>
                <apex:column value="{!v.NCM.FieldB__c}"/>
                <apex:column value="{!v.NCM.FieldC__c}"/>                              
            </apex:pageBlockTable>
        </apex:pageBlockSection>  
        <apex:pageBlockSection rendered="{!NOT(flag)}">

        </apex:pageBlockSection>        
      </apex:pageBlock>
      </apex:form>
    </apex:page>

Best Answer

While you can go up 5 levels in the child to parent direction, you can only go down 1 level in parent to child relationships. See the "Understanding Relationship Query Limitations" section towards the end of the Relationship Queries documentation.

So you will need to use two separate queries. Here is one way to do that (might contain typos):

Map<Id, Child__c> children = new Map<Id, Child__c>([
        Select Id, Name, Contact__c, FieldA__c
        from Child__c
        where Contact__c = :name
        ]);
Map<Id, List<GrandChild__c>> grandChildren = new Map<Id, List<GrandChild__c>>;
for (GrandChild__c grandChild : [
        Select id, Name, Child__c, FieldB__c,
                (Select id, Name, GrandChild__c, FieldC__c from GreatGrandChild__r)
        from GrandChild__c
        where Child__c in :children.keySet()
        ]) {
    List<GrandChild__c> l = grandChildren.get(grandChild.Child__c);
    if (l == null) {
        l = new List<GrandChild__c>;
        grandChildren.put(grandChild.Child__c, l);
    }
    l.add(grandChild);
}
Set<Id> childIds = new Set<Id>();
for(Child__c child : children.values()) {
    List<GrandChild__c> l = grandChildren.get(child.Id);
    if (l = null) {
        l = new List<GrandChild__c>();
    }
    // Wrapper now has extra argument and field, the list of grand children with the
    // great grand children available in each grand child's GreatGrandChild__r field
    wrapList.add(new Wrapper(false, child, l));
}

You will need to check (or just try a few likely values) the name GreatGrandChild__r; normally this would be a plural so it might be GreatGrandChilds__r or GreatGrandChildren__r - that is a choice the person creating the data model makes.

Related Topic