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
-
1You comment to a post answer suggests that you stated problem is not clear enough. An answer was provided and you stated you wanted a work around. Please make your question clearer so that a specific answer can be given. (Like what you have tried and what you are trying to solve)– EricDec 3, 2014 at 15:18
3 Answers
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).
Example:
SELECT OpportunityLineItem.Opportunity.Name, OpportunityLineItem.Opportunity.Owner.Name, Quantity
FROM OpportunityLineItem
- 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.
Example:
SELECT Account_Code__c, (SELECT Amount,IsClosed FROM Opportunities) FROM Account
Reference: Understanding relationships in SOQL.
-
thnx for the reply, but I was looking for ways to access multilevel records in a single table.– MnZDec 3, 2014 at 14:46
-
1well check the scenario one. For that you need to go from child to parent. you can access parent, grandparent and upto 5 levels.– MahmoodDec 3, 2014 at 14:47
-
So if there are multiple grandchilds having same parent, then a Map would be helpful?– MnZDec 3, 2014 at 14:49
-
1Your question only asks about querying objects upto certain levels. I don't know your case or requirement, so can't comment whether or not a map should be used. For multi-level query I have mentioned pretty much what you are allowed to do.– MahmoodDec 3, 2014 at 14:53
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];
and
[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