How to query Products by Opportunity?

Question

I want to query products only related to particular opportunity. And I tried this using sub-queries but I was able to retrieve PricebookEntries related to Opportunities. I'll hit a dead end if I tried joining the results with Product2. How to go about this?

SELECT Id from Product2 where Id in ( 
   SELECT Product2Id  
   FROM PricebookEntry 
   WHERE Pricebook2Id in (Select Pricebook2Id FROM Opportunity WHERE StageName = 'Closed') 
)

Answer 1

You can use OpportunityLineItem to traverse from Opportunity to PricebookEntry and Product..

something like this query

SELECT Id, Name, Account.Name, 
  (SELECT Quantity, UnitPrice, TotalPrice, 
   PricebookEntry.Name, PricebookEntry.Product2.Family FROM 
   OpportunityLineItems) 
FROM Opportunity WHERE Id = :YourOppId

You can also refer this object model to understand the relationship between Product, Pricebook, OpportunityLineItem and Opportunity objects

Answer 2

Try this

select id, quantity, unitPrice, totalPrice, productCode, product2.someFldOnProduct2, 
       opportunity.name, opportunity.amount, opportunity.somefieldOnOppo
    from OpportunityLineItem 
    where opportunity.stageName = 'Closed'

You need to be at Version 30 or higher to do this as the lookup relationship from OpportunityLineItem to Product2 didn't exist until then.

Grouping / aggregating omitted from the above, exercise left to the reader; you could also do summarization/grouping in Apex