+ Start a Discussion
EguiEgui 

SQOL Query get PriceBookEntryId using opportunity info

Hello,

 

I'm stuck on a SOQL query, I want to get the pricebookentryid in a single query to square some selects.

 

I have this :

 

        Id idProduit       = [SELECT Id, Produit__c
                                 FROM Opportunity
                                 WHERE Id =: opp.id].Produit__c;
        
        String codeProduit = [SELECT Id, ProductCode
                                 FROM Product2
                                 WHERE Id =: idProduit].ProductCode;
        Id idPbe           = [SELECT Id
                                 FROM PriceBookEntry
                                 WHERE ProductCode=: codeProduit
                                 AND CurrencyIsoCode =: opp.CurrencyIsoCode].Id;

 

Is it possible to resume it in a single query ?

 

Thanks for your help.

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

This still uses 2 queries, but is concise:

 

PricebookEntry pbe = [SELECT Id,ProductCode,Product2Id FROM PricebookEntry WHERE Id IN (SELECT Produit__c FROM Opportunity WHERE Id = :opp.Id) AND CurrencyIsoCode = :opp.CurrencyIsoCode AND Pricebook2Id = :opp.Pricebook2Id];

* pbe.Product2Id is the same as Opp.Produit__c

* pbe.ProductCode is the same as Opp.Produit__r.ProductCode

* pbe.Id is the final result you are looking for.

 

All Answers

Cory CowgillCory Cowgill

You can use Parent and Child Relationship Queries to minimize the number of SOQL queries.

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_relationships.htm

 

In your example, assuming you have a lookup to Product from Opportunity, you could do this in 2 queries instead of 3.

 

Opportunity oppty = [Select Id,Name,  Product_r.ProductCode, Product__c from Opporunity where Id =: opp.Id limit 1];

PriceBookEntry pbe = [Select Id from PriceBookEntry where ProductCode =: oppty.Product__r.ProductCode and CurrencyIsoCode =: opp.CurrencyIsoCode limit 1];

 

 

sfdcfoxsfdcfox

This still uses 2 queries, but is concise:

 

PricebookEntry pbe = [SELECT Id,ProductCode,Product2Id FROM PricebookEntry WHERE Id IN (SELECT Produit__c FROM Opportunity WHERE Id = :opp.Id) AND CurrencyIsoCode = :opp.CurrencyIsoCode AND Pricebook2Id = :opp.Pricebook2Id];

* pbe.Product2Id is the same as Opp.Produit__c

* pbe.ProductCode is the same as Opp.Produit__r.ProductCode

* pbe.Id is the final result you are looking for.

 

This was selected as the best answer