|
|
|
|
|
Force.com Discussion Boards :
Developer Boards :
Apex Code Development :
Re: Too many SOQL queries: 21
|
|
|
|
|
|
|
Too many SOQL queries: 21
|
|
tschatz
Regular Contributor
Posts: 39
Message 1 of 5
Viewed 10,204 times
|
|
I have done some searching and understand why I'm getting this error, but I don't understand how to work around it.
Code: public class CP_OpportunityClosedWon {
public static void CP_createNewCPUser(Opportunity[] opps){
String cName;
Contact c;
OpportunityLineItem[] oli;
for (Opportunity o:opps){
if (o.Order_Status__c == 'Shipped' || o.Order_Status__c == 'Definite') {
ID cID = [select contactid from opportunitycontactrole where role = 'Ship To' AND opportunityid = :o.id].contactId;
c = [select id, title from contact where id = :cID];
//cName = [select title from contact where id = :cID].title;
oli = [select id from opportunitylineitem where opportunityid = :o.Id];
}
}
for (OpportunityLineItem li : oli){
ID pID = [Select o.PricebookEntryId, o.PricebookEntry.Product2Id from OpportunityLineItem o where id = :li.id].PriceBookEntry.Product2Id;
if(pID != null){
Product2 p = [select id from product2 where id = :pID];
String s = [select name from product2 where id = :pID].name;
Boolean pd = [select downloadable__c from product2 where id = :pID].Downloadable__c;
/*if(s !=null){
o.tempName__c = s;
}*/
if(pd == true){
c.Portal_User__c = true;
c.Portal_URL__c = c.title;
update c;
}
}
}
}
}
I'm getting the error on the line: ID pID = [Select o.PricebookEntryId, o.PricebookEntry.Product2Id from OpportunityLineItem o where id = :li.id].PriceBookEntry.Product2Id;
If I can't put a SOQL query inside of a for loop then how can I accomplish what I am trying to do here?
Any code help or code examples would be appriciated. I have already read about this in the APEX manual but am not grasping what they are saying.
Thank you in advance for your assistance.
_t
|
|
|
|
01-14-2008 07:40 AM
|
|
|
|
|
|
|
Re: Too many SOQL queries: 21
|
|
Kalai
Contributor
Posts: 3
Message 2 of 5
Viewed 10,171 times
|
|
|
Hi,
Instead of updating your contact inside for loop,
Make the contact as Contact[ ] c,
in for loop add each contact inside the Contact array c like c[i].Portal_User__c = true;
update the contact outside the for loop.
It may work..
|
|
|
|
01-16-2008 12:56 AM
|
|
|
|
|
|
|
Re: Too many SOQL queries: 21
|
|
tschatz
Regular Contributor
Posts: 39
Message 3 of 5
Viewed 10,162 times
|
|
|
Thanks for the post but the problem I'm having isn't with updating the Contact, it's with performing the SOQL query in the for loop. How can I iterate through and pull out the different Products from the list of OpportunityLineItems without using a for loop and a SOQL query?
_t
|
|
|
|
01-16-2008 08:14 AM
|
|
|
|
|
|
|
Re: Too many SOQL queries: 21
|
|
HariSubramanian
Visitor
Posts: 1
Message 4 of 5
Viewed 10,143 times
|
|
|
You may want to reduce the number of SOQL queries within the for loop by using joins. Each SOQL statement counts in Apex. In a similar situation, I retrieved product2 columns from opportunity line itself. Something like...
for (OpportunityLineItem oli : [SELECT pricebookentry.product2.<MyCustomFields> FROM OpportunityLineItem WHERE OpportunityId =  .Id])
{
Productlist.add(oli.pricebookentry.product2.<MyCustomField> ;
}
Regards.
|
|
|
|
01-16-2008 10:36 PM
|
|
|
|
|
|
|
Re: Too many SOQL queries: 21
|
|
philbo
Regular Contributor
Posts: 109
Message 5 of 5
Viewed 10,128 times
|
|
|
Hey,
I think there's something wrong with your code, aside from any SOQL governor limit you're blowing. It looks like you're trying to accumulate an array of OpportunityLineItem records as you iterate through your input Opportunity array, but within the loop you're just overwriting the array over and over, instead of appending to it (or better, appending to a Map keyed on OpportunityLineItem ID, to eliminate dupes). I would think that this would invalidate your second loop.
Also, in that same first loop you are assigning a Contact record to the variable 'c' over and over, and then referring to that variable in your second loop, also probably not what you want.
One way you could restructure your code to populate the OpportunityLineItem array would be: <note: code not certified production-ready, or even to compile for that matter :^} >
Code: Set<Id> ocidSet = new Set<Id> ();
for ( Opportunity o : opps ) {
if ( o.Order_Status__c == 'Shipped' || o.Order_Status__c == 'Definite' ) {
ocidSet.add ( o.Id );
}
}
oli = [Select Id , PricebookEntryId , PricebookEntry.Product2Id
from OpportunityLineItem
where OpportunityId in :ocidSet]; Not sure what you're trying to do with your Contact records - but this is the sort of methodology you need to use to keep SOQL queries outside of your loops. Note the extra fields pulled from OpportunityLineItem in that 'select', removing the need altogether for that first 'select' statement in your second loop (which needs the same sort of attention as shown above, to get those other three embedded SOQL queries out of there)
Hope this helps
-philbo
Phil Matthews
Tier1CRM Inc.
www.tier1crm.com
|
|
|
|
01-17-2008 07:35 AM
|
|
|
|
|
|
|
|
|
|
|
