2

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

Improve this question
1
  • 1
    You 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)
    – Eric
    Dec 3, 2014 at 15:18

3 Answers 3

Reset to default
3

There are 2 scenarios:

  1. 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

  1. 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.

Improve this answer
4
  • thnx for the reply, but I was looking for ways to access multilevel records in a single table.
    – MnZ
    Dec 3, 2014 at 14:46
  • 1
    well check the scenario one. For that you need to go from child to parent. you can access parent, grandparent and upto 5 levels.
    – Mahmood
    Dec 3, 2014 at 14:47
  • So if there are multiple grandchilds having same parent, then a Map would be helpful?
    – MnZ
    Dec 3, 2014 at 14:49
  • 1
    Your 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.
    – Mahmood
    Dec 3, 2014 at 14:53
1

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];
Improve this answer
1
  • Thanks. But I was looking for a workaround.
    – MnZ
    Dec 3, 2014 at 14:46
1

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
Improve this answer

You must log in to answer this question.

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