+ Start a Discussion
lagelage 

Query join Product2 nand PriceBookEntry

I have seen a lot of discussion on joined querries lately.  We are having to make multiple quierries to pull in the Product and pricing information to automate the addition of products to an existing Opportunity.  Currently we use two querries:
 
Select ID,ProductCode, Name, Set_Quantity__c, Set_Value__c, Repair_Lead_Time__c from Product2 where + condition 
 
The above query retrieves the product information, but then we have to make another round trip using the ID against the PriceBookEntry to get the default pricing information - like this:
 
Select ID from PricebookEntry where Product2Id = + product ID from above
 
Is there a way to make one query instead of two?  Doing this for each product is intense and time consuming to display a list with pricing information.
cheenathcheenath
This will do the job:

SELECT Id, Name, ProductCode, (SELECT Id FROM PricebookEntries) FROM Product2




lagelage
Thanks for the assist, but it still does not work (I corrected it from PricebookEntries to PricebookEntry - but no difference).  The error I get in the S-Control is:
 
"sObect type 'T' is not supported."
 
If I remove the inner query "(SELECT Id FROM PricebookEntry)", the query runs fine, but then I still have to make another query to get the PricebookEntry Id for the product selected.
lagelage

This works:

SELECT Id, Name, ProductCode, Repair_Lead_Time__c, Set_Quantity__c, Set_Value__c, (SELECT Id FROM PricebookEntries) FROM Product2  Limit 20

This does not work (adding Where clause):

SELECT Id, Name, ProductCode, Repair_Lead_Time__c, Set_Quantity__c, Set_Value__c, (SELECT Id FROM PricebookEntries) FROM Product2  Where ProductCode = "BLADE"

cheenathcheenath
Try this:

SELECT Id, Name, ProductCode, Repair_Lead_Time__c, Set_Quantity__c, Set_Value__c, (SELECT Id FROM PricebookEntries) FROM Product2  Where ProductCode = 'BLADE'


lagelage
Same error :-(
 
binding.SetServerUrl('{!API.Enterprise_Server_URL_40}');
binding.SessionId = '{!API.Session_ID}';
 
queryResult = binding.Query("SELECT Id, Name, Repair_Lead_Time__c, Set_Quantity__c, Set_Value__c, (SELECT Id FROM PricebookEntries) FROM Product2 Where ProductCode = 'BLADE'",false);
 
e = new Enumerator(queryResult);
cheenathcheenath
It seems you are using the beta toolkit. I dont think it supports API 8.0.
You need to upgrade to the production toolkit. Here are some docs to
get you started:

[1] https://wiki.apexdevnet.com/index.php/API#AJAX
[2] http://www.salesforce.com/us/developer/docs/ajax80/apex_ajax.pdf




lagelage

Nope, using API 9 -

Salesforce.com Enterprise Web Services API Version 9.0
Generated on 2007-03-15 11:57:59 +0000.

I will read the documentation though.  If you think of anything else let me know.  Thanks for the link to the APEX toolkit, it does make it a little easier to code :-)

lagelage

Found the problem - you were on.  We had not updated this code since version 5.  It was using bindings and such in combination with javascript and SOAP.  Once we modified it to AJAX 9, life was beautiful.  The query you recommended works great and performance was improved 10 fold!  MANY MANY THANKS for pointing me in the right direction.

 

Final code:

sforce.connection.login("username", "password");

var result = sforce.connection.query("SELECT Id, Name, ProductCode, Repair_Lead_Time__c, Set_Quantity__c, Set_Value__c, (SELECT Id FROM PricebookEntries) FROM Product2 where ProductCode = 'BLADE'" {

onSuccess : success, onFailure : failure});

function success(result) {

var queryResult = new sforce.QueryResultIterator(result);

while(queryResult.hasNext()){

var record = queryResult.next();

//...iterate through records

Access to PricebookEntry was found by using: 

record.PricebookEntries.records.Id

cheenathcheenath
One more thing, if you are using this code inside an s-control
then there is no need to to make the login call.  New  toolkit  knows
how to get the session from scontrol.