[SalesForce] Single SOQL query to list ALL Products with ALL Quote Line Items and ALL Order Products

While both Quote Line Item and Order Product have a lookup fields to Product, no relationship name is shown in Workbench.

Further digging indicates a somewhat hidden PriceBookEntry object exists in between.

OK, but since we can't query down more than one level, is there any easy way to make a single SOQL query to list ALL Products with ALL Quote Line Items & ALL Order Products?

It seems like it would be a basic thing, but I am stumped. Schoolboy error?

For example – the following simply does not work, even though there are lookup fields from both OrderItem and QuoteLineItem to Product:

Select id, (Select id from OrderItems), (Select id from QuoteLineItems) FROM Product2

Best Answer

As you can see from Workbench, there are no relationshipNames for Product2 to OrderItem and Product2 to QuoteLineItem. Yet you can query from Product2 to PricebookEntry

enter image description here

However, you can query from PricebookEntry to both OrderItem and QuoteLineItem

so you could do:

SELECT id, Product2.Name, (select Id from OrderItems), (select Id from QuoteLineItems) 
  FROM PricebookEntry

or

SELECT id, Product2.Name, (select id from OrderItems), (select id from QuoteLineItems) 
  FROM PricebookEntry 
  WHERE Product2.ProductCode IN ('foo','bar')

and you can add whatever lookup fields you need to get Order details or Quote details

If in a multicurrency org, add CurrencyIsoCode to the returned fields or include in Where clause to get a single currency. If in a multi-pricebook org , add Pricebook2.Name to returned fields or WHERE clause

Related Topic