function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
oaklandoakland 

3 level deep nested soql query

Define Parent Object:Foo with Standard Field "Name".
Define Child Object: Bar with Standard Field "Name" and Custom Field Lookup(Foo) (with Child Relationship Name Bars).
Define Grandchild Object: Ziff with Standard Field "Name" and Custom Field Lookup(Bar) (with Child Relationship Name Ziffs).

Background:

 

1. This queries the Parent Foo and makes elements of Bar visible:

Select Name,(Select Name from Bars__r) from Foo__c


2. This queries the Child Bar  and makes elements of Foo visible:

Select Name, Foo__r.Name From Bar__c where Foo__c  !=  null

3. These queries in the Grandchild work:

 

Select Name,  Bar__r.Name From Ziff__c

Select Name,  Bar__r.Name, Bar__r.Foo__r.Name From Ziff__c


I'm seeking a query on the Parent Foo, similar to #1, that makes elements of Bar and Ziff visible.

 

Thanks.

 

 

 

Bhawani SharmaBhawani Sharma
You can fire an aggregate query here using group by rollup like :
Select Foo foo, Bar bar, count(Id ) from Ziff group by ROLLUP(Foo, Bar)

Or

Make id Simple like
First query :
Select Id, (Select Id from Bars) from Foo where ....4
Map<Foo, List<Bar>> ......

and
Select Id, (Select Id from Ziffs) from Bar where ....4
Map<Bar, List<Ziff>> ......

Create a wrapper class like
class ( Foo, List<Bar>, List<List<Ziff>> )

Use thsi Wrapper class to draw the records
oaklandoakland

Thanks Tech Force SC.

 

Is there a reason you followed up with a tip about making an aggregate query?

 

I just want a SOQL query that returns a parent, all it's children and all it's children's grandchildren.

 

Is a wrapper class a common design pattern for this? Or something that occurred to you?

 

Thanks.

 

 
Bhawani SharmaBhawani Sharma
Actually aggregate query helps in getting data in one shot. You can use aggregate results grouped by some data. This works exactly similar as Summary reports.

Wrapper class is best way to combine different type of data. As per your requirement, you want to hold Parent, Children and Sub-children all together. So wrapper class would be best.
oaklandoakland

Neither of SOQL statements compile... But here is an attempt at what I'm looking for....


Select Name,(Select Name from Bars__r),
(Select Bars__r.Ziffs__r.Ziff__c.Name from Bars__r)
 from Foo__c

 

Or


Select Name,(Select Name from Bars__r),
(Select Name from Bars__r.Ziffs__r)
 from Foo__c

 

That is, I'd like to select from the Relationship Name into another Relationship Name.

 

I'd prefer not to go down the path of creating a wrapper class every time I want to join 3 levels deep.

 

Aside from that - the SOQL query for wrapper class isn't that straight forward.

 

Thanks

 

 
crop1645crop1645

From the SFDC doc (as of V27.0):

 

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.

 
oaklandoakland

Thanks crop1645.

 

What is the name of the document you're citing in the SFDC doc?

 

The doc would be better if it said (paraphased) that you can select up many levels but down only a single level.

 
crop1645crop1645

The doc is here: http://www.salesforce.com/us/developer/docs/soql_sosl/index.htm
Bullfrog84Bullfrog84
The link, documentation has been updated,

You can actually get three levels deep now.

http://www.salesforce.com/us/developer/docs/soql_sosl/index.htm
Leonardi KohLeonardi Koh

That's incorrect... you cannot go any further than 1 level deep for the nested soql query
So you can only go
[SELECT Id, (SELECT Id FROM Contacts) FROM Account]

But you cannot go deeper say
[SELECT Id, (SELECT Id, (SELECT Id FROM xxx) FROM Contacts) FROM Account]

What you CAN do is access a field with relationship multiple level deep for example:
[SELECT Id, Contact.xxx.yyy.bbb FROM Case]