function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AntonyasLenAntonyasLen 

Product2 x PricebookEntry SOQL Query

Hi,

 

I'm currently creating invoices from an Opportunity O.

So i needed to retrieve my product to attach them to an Invoice I ( my invoice will have InvoiceLineItems)

So i did this SOQL request:

 

PricebookEntry pe = [Select Product2Id from PricebookEntry WHERE id = :opps.PricebookEntryId];

But i also need to get the weight (custom field) from my Product2 object (i only need it for the invoices so my OpportunityLineItem will not have it.)

I was thinking about making an other request as :

 

Product2 pWeight = [select Product_Weight__c from Product2 where Id = :pe.Product2Id];

 

So far it's working but my class has to many SOQL request and i'm trying to optimize some request, i know that Product2 and PricebookEntry have a special relation so i was thinking that i can put my two request in one unique request.

Do you think that is possible?

 

Best Answer chosen by Admin (Salesforce Developers) 
admintrmpadmintrmp
PricebookEntry pe = [Select Product2Id, Product2.Product_Weight__c from PricebookEntry WHERE id = :opps.PricebookEntryId];

 

Would this not work for you?

 

If you are having too many SOQL executions in a single invocation, you need to do some serious optimization.

All Answers

admintrmpadmintrmp
PricebookEntry pe = [Select Product2Id, Product2.Product_Weight__c from PricebookEntry WHERE id = :opps.PricebookEntryId];

 

Would this not work for you?

 

If you are having too many SOQL executions in a single invocation, you need to do some serious optimization.

This was selected as the best answer
AntonyasLenAntonyasLen

I tried but it wasn't working, now it's fine ( maybe i didn't write it well ..).

 

I know that i need a serious optimization, but i need to work on Opportunity OpportunityLineItem and the product..@_@
admintrmpadmintrmp

Ah yes, I feel your pain. There is a lot of querying that needs to go on before you can get results when working with opportunities and products.
AntonyasLenAntonyasLen

for example, cross my OpportunityLineItem and Opportunity query by a subquery sould be the best practice ?

but on need more criteria  @_@ it's kind a mess and as you said painfull hahaha
admintrmpadmintrmp

Where possible, use relationships within your queries. It should be possible to separate your result sets using the following code if you need to:

 

Pricebook2 pb = (Pricebook2)pe.Pricebook2;

 

I know you can do this on custom objects, so I don't see why it would be impossible with standard objects.
AntonyasLenAntonyasLen

i'll check it! thanks
sfdcfoxsfdcfox

The cast isn't necessary; a relationship "field" is automatically of the same SObjectType as the object it refers to.

 

Ex:

 

contact c = [select id,account.id,account.name from contact limit 1];
account a = c.account;

 
AntonyasLenAntonyasLen

do you think that i can optimize this?

 

  		
  		List<OpportunityLineItem> oli =[SELECT UnitPrice, TotalPrice, Quantity,
  									  	Produit_id_uad__c, PricebookEntryId, MPN__c, ListPrice,Image_Url__c, Id_uad__c, Discount, Description, Delivery_date__c, Customization__c 
  									       FROM OpportunityLineItem 
  									  	WHERE OpportunityId = :opID
  									   	AND Delivery_date__c = :deli];
  									   	
		Opportunity opo = [SELECT 	HaveTax__c,Id, Global_Discount_tmp__c, Delivery_Cost__c, 
									AccountId,Delivery_Contact__c,Global_Discount__c 
						   FROM Opportunity  WHERE Id = :opID];

 
sfdcfoxsfdcfox

If you assume that there will be at least one line item, you can do this:

 

OpportunityLineItem[] oli = 	[SELECT UnitPrice, TotalPrice, Quantity, Produit_id_uad__c, PricebookEntryId, MPN__c, ListPrice,Image_Url__c, Id_uad__c, Discount, Description,
					Delivery_date__c, Customization__c, Opportunity.Id, Opportunity.HaveTax__c, Opportunity.Global_Discount_tmp__c, Opportunity.Delivery_Cost__c,
					Opportunity.AccountId, Opportunity.Delivery_Contact__c, Opportunity.Global_Discount__c 
				FROM OpportunityLineItem
				WHERE OpportunityId = :opId AND Delivery_Date__c = :deli];
Opportunity opo = oli[0].Opportunity;

This reduces your query count from 2 to 1. Without that assumption, it would be best to just stick with what you have.
AntonyasLenAntonyasLen

Thanks for your answer, it's very usefull for me (i can see how to cross request because it's a bit mess for me)

But i need all the Line Item,because i'm making a For loop on it !

The idea is to get every Line Item with the same "delivery date" and for each delivery date i'll create an invoice with the corresponding Line Item.

So as you said i'll stick with my two queries !

 

But thanks you to show me your example!!!