+ Start a Discussion
MC2014MC2014 

Dynamic Soql can't do child to parent look up?

So I am playing around with LREngine library, a roll up summary alternative in apex by Tgerm.com.

This is the query it hows up in debug
SELECT OpportunityId, Sum(TotalPrice) lre0 FROM OpportunityLineItem WHERE OpportunityId in :masterIds AND Product2.Class__c = 'Science' GROUP BY OpportunityId

I would get this exception:
System.QueryException: No such relation 'Product2' on entity 'OpportunityLineItem'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

The thing is if I were to execute that query in Query Editior, it execute fine. Is it cause It is using database.query call?





Best Answer chosen by MC2014
KevinPKevinP
Looking at this ERD diagram: https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_erd_products.htm it looks like you need to use PricebookEntry to get to the product2. Something like:

SELECT OpportunityId, Sum(TotalPrice) lre0 FROM OpportunityLineItem WHERE OpportunityId in :masterIds AND PricebookEntry.product2.Class__c = 'Science' GROUP BY OpportunityId