Salesforce and SOQL : Accessing a grandchild object from the Grandparent

Question

I'm attempting to access a grandchild object.

I have 3 Objects,

     Opportunity,
          Quote,
              QuoteLineItems,

Opportunity is a Parent to Quote and Quote is a Parent to QuoteLineItems.

Unfortunately, writing a query for this is proving challenging. I'm using Force.com explorer and there's no direct relationship from Opportunity to QuoteLineItems. You can can only nest Select statements one level deep.

I'm looking to write a query that will grab all the fields from these object (I can manually enter the fields) but I'm not sure how the join logic works.

Select ID, (Select ID From Quotes) From Opportunity.

Somehow I need to join in the Quotelineitems in this query.

Any thoughts?

Answer 1

From the API docs "In each specified relationship, only one level of parent-to-child relationship can be specified in a query."

Going the other direction (child-to-parent), you can traverse five levels. So, something like this may work for you:

SELECT Id, Quantity, Quote.Name, Quote.Opportunity.Name FROM QuoteLineItem ...

Answer 2

I struggled so hard to figure this out, and the answer to this posting was what finally helped me understand. Very few examples out here on the web!

The issue for me was that the order that you put the relationships for a child-to-grandparent is counter-intuitive. This is how it works:

(parent).(grandparent).(field)

(parent).(grandparent).(great-grandparent).(field)

As noted, you can go up to 5 levels.

Some examples (including the one from the answer here):

Relationship chain from grandparent to parent to child: Opportunity - Quote - QuoteLineItem

Sample query:

SELECT Id, Quantity, Quote.Name, Quote.Opportunity.Name FROM QuoteLineItem

Relationship chain from grandparent to parent to child: Account - Bill_to_Contact__r (custom) - Quote

Sample query:

SELECT Name, License_To_Contact__r.Name, Bill_To_Contact__r.Account.Name FROM Quote