[SalesForce] How to find out relationships for SOQL queries

I want to construct a SOQL query displaying all Opportunities that are sorted by the Products that are associated to it.

I however have a problem finding out how Opportunities and Opportunity Products are related.

So far, I can query all closed opportunities by the following SOQL:

SELECT Amount,CloseDate,Name FROM Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST

This shows me all Opportunities with Amount and Name.
But I would like to also display the related Opportunity Product and sort it by those and sum by Opportunity Product.

Just to show the products for opportunities works like this:

SELECT Name FROM Product2

I just found out that the object is called Product2.
But I wonder how to combine the two objects (parent-to-child? child-to-parent? child-to-child?) and have no idea how to exactly find that out.

Do you have advice for me, both specific and more general for creating similar queries?

Edit: I should add that I want to group the results by PriceBookEntry, i.e. I want to see all PriceBook entries and the related opportunities.

Best Answer

You do a sub-select, e.g:

Select Amount,CloseDate,Name, (Select PricebookEntry.Product2Id, TotalPrice, UnitPrice, ListPrice From OpportunityLineItems) From Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST

Opportunity Products (OpportunityLineItem) is a Detail to the Master Opportunity, i.e. 1:n between Opportunity and Product lines. Products belong to a Pricebook, thus you have to traverse the PricebookEntry relationship to retrieve the Product ID (if you need it).

You can also query having OpportunityLineItem as the Primary object and referencing Opportuinty via the Opportunity relationship:

Select Select PricebookEntry.Product2Id, TotalPrice, Opportunity.Amount, Opportunity.CloseDate, Opportunity.Name From OpportunityLineItem WHERE Opportunity.StageName = 'Closed' ORDER BY Opportunity.Amount

You can't go directly from Product2 to Opportunity because there is no direct relationship between the two, i.e. the relationship is via PricebookEntry.

A useful tool in discovering relationships between objects is to use the Force.com Eclipse plugin. You can double click on the salesforce.schema item and it will open a GUI for building queries:

iMAGE

Related Topic