You need to sign in to do that
Don't have an account?
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;
}
}
}
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
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.
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
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;
}
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];