How to find out relationships for SOQL queries?

Question

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.

Answer 1

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:

Answer 2

Here's a schematic of the relationships involved.

Products are related to Pricebook via the PriceBookEntry (junction) Object. OpportunityLineItems look up to the PriceBookEntry Object, rather than looking up directly to Product2.

Therefore if you want to select Products with Opportunities, it will have to be via the PriceBookEntry Relationship

Select Id, Name, (Select Id, PriceBookEntry.Product2Id, PriceBookEntry.Product2.Name from OpportunityLineItems) from Opportunity WHERE StageName = 'Closed' ORDER BY Amount DESC NULLS LAST

Querying on OpportunityLineItem

Select PriceBookEntryId, Opportunity.Name from OpportunityLineitem WHERE Opportunity.StageName = 'Closed' group by PriceBookEntryId, Opportunity.Name

Answer 3

The schema builder tool is of great help too.This is present in the org.As you could see the Product and Oppurtunity related through a junction object called Oppurtunity Product (Oppurtunity Line Item) and you may use eclipse to find the suitable queries .