+ Start a Discussion
tschatztschatz 

Too many SOQL queries: 21

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

KalaiKalai

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..

 

 

tschatztschatz
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
HariSubramanianHariSubramanian
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 = :o.Id])
{
Productlist.add(oli.pricebookentry.product2.<MyCustomField>);
}
 
Regards.
philbophilbo
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