+ Start a Discussion
ToddKruseToddKruse 

Apex class question regarding queries

I am receiving the error: caused by: System.Exception: Too many SOQL queries: 101

 

My logic is below.  What I am trying to do:

 

1.  retrieve a list of all opportunities that match a certain criteria

2.  if that opportunity doesn't have any projects, update a custom field in the opportunity table (is_pipeline__c)

3.  if there is a project, if its project_stage__c = 'pipeline', update that is_pipeline__c filed

 

From my readings, I am hitting the 100 limit because for each iteration through my for loop, we are calling at least 1 soql statement and that will reach the limit fast.  Any thoughts on how I can get around this issue with my class code below?  Thanks 

 

     List<Opportunity> allOpps = [SELECT Id, Is_Pipeline__c FROM Opportunity WHERE billingtype__c <> null AND pldepartment__c <> null  AND Is_Pipeline__c = false AND stagename <> 'Closed Won']; //limit 60];

    

     for (Opportunity oppys : allOpps)

     {

    

     Integer existingProjects = [select count() from SFDC_Projects__c where opportunity__c = : oppys.Id];

    

     if (existingProjects < 1)

     {

     oppys.Is_Pipeline__c = true;

     update oppys;

     }

     else

     {

     SFDC_Projects__c project = [SELECT Project_Stage__c FROM SFDC_Projects__c WHERE opportunity__c = : oppys.Id];

    

     if (project.Project_Stage__c == 'Pipeline')

     {

     oppys.Is_Pipeline__c = true;

     update oppys;

     }

     }

    

    

Best Answer chosen by Admin (Salesforce Developers) 
gm_sfdc_powerdegm_sfdc_powerde

Use SOQL query to retrieve opportunity records in bulk as follows -

for (List<Opportunity> opptys: [SELECT Id, Is_Pipeline__c FROM Opportunity WHERE billingtype__c <> null AND pldepartment__c <> null AND Is_Pipeline__c = false AND stagename <> 'Closed Won']) { // Here you can get existing project count in bulk thus avoiding a SOQL for each project. }

Also, you might want to store the oppty sobjects being updated in a list and do a bulk update at the end of for loop This makes your code complex, but you are guarded against hitting governer limits on # of SOQLs, # of DML statements, # of records returned etc.

 

Even better, if this is not being run as part of a user transaction, consider using batch API.


 

All Answers

gm_sfdc_powerdegm_sfdc_powerde

Use SOQL query to retrieve opportunity records in bulk as follows -

for (List<Opportunity> opptys: [SELECT Id, Is_Pipeline__c FROM Opportunity WHERE billingtype__c <> null AND pldepartment__c <> null AND Is_Pipeline__c = false AND stagename <> 'Closed Won']) { // Here you can get existing project count in bulk thus avoiding a SOQL for each project. }

Also, you might want to store the oppty sobjects being updated in a list and do a bulk update at the end of for loop This makes your code complex, but you are guarded against hitting governer limits on # of SOQLs, # of DML statements, # of records returned etc.

 

Even better, if this is not being run as part of a user transaction, consider using batch API.


 

This was selected as the best answer
ToddKruseToddKruse

Thanks for the help in creating the  for list.  However, I am not familiar with how to complete the section you talked about in the commented section....retrieving the project count in bulk.  Could you clarify please?

 

Thanks

 

--Todd 

BritishBoyinDCBritishBoyinDC

Depending on the relationship between Opportunity and Project, you could also try using a related query to pull it all in the data in one query. Something like this:

 

List <SFDC_Projects__c> oproj = new List <SFDC_Projects__c> ();

List <Opportunity> opptys = new List <Opportunity> ();

 

opptys = [Select Id, Is_Pipeline__c, (select Id, Project_Stage__c from SFDC_Projects__c) from Opportunity WHERE billingtype__c <> null AND pldepartment__c <> null AND Is_Pipeline__c = false AND stagename <> 'Closed Won'];

for (Opportunity o: opptys) {

oproj = o.SFDC_Projects__c; //You'll need to find out the relationship name for the relationship to SFDC_projects__c//

 

Now you have a list of related projects, so you can execute your logic by checking the size of o.Proj

if (oproj.size() <1) {

o.Is_Pipeline__c = true;

}

else

{

for (SFDC_Projects__c sp: oproj) {

if (sp.Project_Stage__c == 'Pipeline'){

o.Is_Pipeline__c = true;}

}

}

update opptys;

}

 

 

Message Edited by BritishBoyinDC on 11-25-2009 08:49 PM
Message Edited by BritishBoyinDC on 11-25-2009 08:51 PM
gm_sfdc_powerdegm_sfdc_powerde

To get the records from SFDC_Project__C in bulk, use this SOQL

[SELECT opportunity__c, Project_Stage__c FROM SFDC_Projects__c WHERE opportunity__c in: opptys];