Apex Code Development You need to sign in to do that
Don't have an account?
Kuliza Dev Getting SF error "Too many SOQL queries: 101"
Hi
I am facing below error for my trigger
SF error "Too many SOQL queries: 101"
Below are my trigger and class
trigger :
trigger rollUpTasks on Task (after delete, after insert,
after undelete) {
//if the task is an insert, update, or undelete
if(trigger.isInsert || trigger.isUnDelete){
for(Task t :Trigger.new){
//if the task is on a Lead
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('00Q')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions = new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.new,
'Task', 'WhoID', 'Lead', 'and IsClosed = True ');
}
//if task is on a Contact
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('003')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions =
new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.new,
'Task', 'WhoID', 'Contact', 'and IsClosed = True ');
}
}
}
//if the task is a delete
if(trigger.isDelete){
for(Task t :Trigger.old){
//if the task is on a Lead
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('00Q')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions = new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.old,
'Task', 'WhoID', 'Lead', 'and IsClosed = True');
}
//if task is on a Contact
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('003')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions =
new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.old,
'Task', 'WhoID', 'Contact', 'and IsClosed = True ');
}
}
}
}
Class :
RollUpSummaryUtility class:
public class RollUpSummaryUtility {
//the following class will be used to house the field names
//and desired operations
public class fieldDefinition {
public String operation {get;set;}
public String childField {get;set;}
public String parentField {get;set;}
public fieldDefinition (String o, String c, String p) {
operation = o;
childField = c;
parentField = p;
}
}
public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
list<sObject> records, String childObject, String childParentLookupField,
String parentObject, String queryFilter) {
//Limit the size of list by using Sets which do not contain duplicate
//elements prevents hitting governor limits
set<Id> parentIds = new set<Id>();
for(sObject s : records) {
parentIds.add((Id)s.get(childParentLookupField));
}
//populate query text strings to be used in child aggregrator and
//parent value assignment
String fieldsToAggregate = '';
String parentFields = '';
for(fieldDefinition d : fieldDefinitions) {
fieldsToAggregate += d.operation + '(' + d.childField + ') ' +', ';
parentFields += d.parentField + ', ';
}
//Using dynamic SOQL with aggergate results to populate parentValueMap
String aggregateQuery = 'Select ' + fieldsToAggregate +childParentLookupField + ' from ' + childObject + ' where ' +childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +' group by ' + childParentLookupField;
//Map will contain one parent record Id per one aggregate object
map<Id, AggregateResult> parentValueMap = new map <Id, AggregateResult>();
for(AggregateResult q : Database.query(aggregateQuery)){
parentValueMap.put((Id)q.get(childParentLookupField), q);
}
//list of parent object records to update
list<sObject> parentsToUpdate = new list<sObject>();
String parentQuery = 'select ' + parentFields + ' Id ' +' from ' + parentObject + ' where Id IN :parentIds';
//for each affected parent object, retrieve aggregate results and
//for each field definition add aggregate value to parent field
for(sObject s : Database.query(parentQuery)) {
Integer row = 0; //row counter reset for every parent record
for(fieldDefinition d : fieldDefinitions) {
String field = 'expr' + row.format();
AggregateResult r = parentValueMap.get(s.Id);
//r will be null if no records exist
//(e.g. last record deleted)
if(r != null) {
Decimal value = ((Decimal)r.get(field) == null ) ? 0 :
(Decimal)r.get(field);
s.put(d.parentField, value);
}
else {
s.put(d.parentField, 0);
}
row += 1; //plus 1 for every field definition after first
}
parentsToUpdate.add(s);
}
//if parent records exist, perform update of all parent records
//with a single DML statement
if(parentsToUpdate.Size() > 0) {
update parentsToUpdate;
}
}
}
I am facing below error for my trigger
SF error "Too many SOQL queries: 101"
Below are my trigger and class
trigger :
trigger rollUpTasks on Task (after delete, after insert,
after undelete) {
//if the task is an insert, update, or undelete
if(trigger.isInsert || trigger.isUnDelete){
for(Task t :Trigger.new){
//if the task is on a Lead
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('00Q')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions = new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.new,
'Task', 'WhoID', 'Lead', 'and IsClosed = True ');
}
//if task is on a Contact
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('003')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions =
new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.new,
'Task', 'WhoID', 'Contact', 'and IsClosed = True ');
}
}
}
//if the task is a delete
if(trigger.isDelete){
for(Task t :Trigger.old){
//if the task is on a Lead
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('00Q')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions = new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.old,
'Task', 'WhoID', 'Lead', 'and IsClosed = True');
}
//if task is on a Contact
if(t.WhoId != null && String.valueof(t.WhoId).startsWith('003')){
list<RollUpSummaryUtility.fieldDefinition> activityDefinitions =
new list<RollUpSummaryUtility.fieldDefinition> {
new RollUpSummaryUtility.fieldDefinition('COUNT', 'ID', 'Total_Num_of_Activities__c')
};
RollUpSummaryUtility.rollUpTrigger(activityDefinitions, trigger.old,
'Task', 'WhoID', 'Contact', 'and IsClosed = True ');
}
}
}
}
Class :
RollUpSummaryUtility class:
public class RollUpSummaryUtility {
//the following class will be used to house the field names
//and desired operations
public class fieldDefinition {
public String operation {get;set;}
public String childField {get;set;}
public String parentField {get;set;}
public fieldDefinition (String o, String c, String p) {
operation = o;
childField = c;
parentField = p;
}
}
public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
list<sObject> records, String childObject, String childParentLookupField,
String parentObject, String queryFilter) {
//Limit the size of list by using Sets which do not contain duplicate
//elements prevents hitting governor limits
set<Id> parentIds = new set<Id>();
for(sObject s : records) {
parentIds.add((Id)s.get(childParentLookupField));
}
//populate query text strings to be used in child aggregrator and
//parent value assignment
String fieldsToAggregate = '';
String parentFields = '';
for(fieldDefinition d : fieldDefinitions) {
fieldsToAggregate += d.operation + '(' + d.childField + ') ' +', ';
parentFields += d.parentField + ', ';
}
//Using dynamic SOQL with aggergate results to populate parentValueMap
String aggregateQuery = 'Select ' + fieldsToAggregate +childParentLookupField + ' from ' + childObject + ' where ' +childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +' group by ' + childParentLookupField;
//Map will contain one parent record Id per one aggregate object
map<Id, AggregateResult> parentValueMap = new map <Id, AggregateResult>();
for(AggregateResult q : Database.query(aggregateQuery)){
parentValueMap.put((Id)q.get(childParentLookupField), q);
}
//list of parent object records to update
list<sObject> parentsToUpdate = new list<sObject>();
String parentQuery = 'select ' + parentFields + ' Id ' +' from ' + parentObject + ' where Id IN :parentIds';
//for each affected parent object, retrieve aggregate results and
//for each field definition add aggregate value to parent field
for(sObject s : Database.query(parentQuery)) {
Integer row = 0; //row counter reset for every parent record
for(fieldDefinition d : fieldDefinitions) {
String field = 'expr' + row.format();
AggregateResult r = parentValueMap.get(s.Id);
//r will be null if no records exist
//(e.g. last record deleted)
if(r != null) {
Decimal value = ((Decimal)r.get(field) == null ) ? 0 :
(Decimal)r.get(field);
s.put(d.parentField, value);
}
else {
s.put(d.parentField, 0);
}
row += 1; //plus 1 for every field definition after first
}
parentsToUpdate.add(s);
}
//if parent records exist, perform update of all parent records
//with a single DML statement
if(parentsToUpdate.Size() > 0) {
update parentsToUpdate;
}
}
}
This error occur only when you write SOQL query in Loop and that loop iterate more then 100 time.
Because we only fire 100 SOQL.
Regards
VIrendra
Please follow the best practice to write Apex Code, visit this link https://developer.salesforce.com/page/Apex_Code_Best_Practices whic cover all best practice Method to write apex class which defiantly help you to get rid of Salesforce Limit Exception.
In this scenario, what is happening is you have a trigger that is firing after the insert or delete (or undelete) action on a record, and it is doing some fairly heavy lifting.. what often happens with triggers is Salesforce will "bundle together" their calling when lots of records are being affected (such as during data loading or mass edit) and then throw 200 of them into the trigger all at once (hense the trigger.new array) - this means the trigger is only called once in say a one second period instead of 200 times. Thus saving execution effort.
This means that the triggers need to be written in such a way that this is actually beneficial, which means often using lots of maps and lists to store data within the scope of the trigger rather than shooting off to the database too many times. The links provided by the other answers will help you out a lot here, I'd be suprised if anyone can just refactor all that code for you - so you will have to read/learn the process yourself.
The key things is in your code, you cannot call out from your trigger "for every record in trigger.new" to another class that makes a SOQL query. You need to form more maps and lists in memory and traverse them instead.
You need to remove the key bit you have in the class that calls the SOQL query. The most common approach to handling SOQL limit stuff is as follows:
Iterate through trigger.new and make a list of all the IDs (or child IDs) you are about to deal with
Create a new Map<Id, sObject>
Do ONE SOQL query to load up all the relevant sObjects into the map, indexed by their ID
Keep hold of that map in memory (and pass it as a parameter into other classes)
When you are about to do a SOQL Select - use a map.get(Id) instead....
Ok?
Literally think of the Map as being a tiny "local database" of the records you are after and instead of hitting up SOQL, "get" from your map instead. If you need multi-selects you can iterate through your map as much as you like (Within reason) because the govenor limits (such as CPU time) on that kind of operation are much kinder than tha 100 SOQL query limit.