What is best way to display child, grandchild from parent or more than 1 level SOQL down. I tried to display grandchild of a parent record. Any reference would be great. Thanks
There are 2 scenarios:
- 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).
SELECT OpportunityLineItem.Opportunity.Name, OpportunityLineItem.Opportunity.Owner.Name, Quantity
- 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.
SELECT Account_Code__c, (SELECT Amount,IsClosed FROM Opportunities) FROM Account
Reference: Understanding relationships in SOQL.
I think you can’t go two levels downwards in one SOQL.
Example:Account –> Opportunities –> Opportunity Contact Roles
You can’t do this:
[SELECT Id, (SELECT Amount, (SELECT Id FROM OpportunityContactRoles) FROM Opportunities) FROM Account];
But you can do this:
[SELECT Id, (SELECT Amount FROM Opportunities) FROM Account];
[SELECT Id, (SELECT Id FROM OpportunityContactRoles) FROM Opportunity WHERE Id IN :myOppList];
This is a sample going three levels deep using custom relationships
Notice I started from the Line Items and work my way up, the last one which is the Account Object looks like: Opportunity_Product_Line__c.Opportunity__r.Account.Name
Select Opportunity__r.Id, Opportunity__r.Name, Opportunity__r.CloseDate, Enabling_Technology__c, Product_Id__c, Prod_Name__c, Opportunity_Product_Line__c.Opportunity__r.Account.Name FROM Opportunity_Product_Line__c
You can also go from the middle and go one level up and one level down, so from Opportunities, In case that you need all Opportunities whether they have Line Items or not
Select Id, Name, CloseDate, Account.Name, (Select Enabling_Technology__c, Product_Id__c, Prod_Name__c from Opportunity_Products__r) FROM Opportunity