Trigger on Task to count open tasks giving error: Too many SOQL

Question

I have a trigger on task to count open tasks on opportunity. It is working fine when we create single task at a time but when I create log a call and follow up task on same time it gives me error. Please find below screen shots:

Below is my trigger code:

trigger OpenTaskCount on Task (after insert, after update, after delete, after undelete) { 

List<Task> taskList = (Trigger.isInsert|| Trigger.isUnDelete) ?   Trigger.new : Trigger.old; 

 List<Id> taskIds = new List<Id>(); 
 for (Task tsk : taskList) { 
     taskIds.add(tsk.WhatID); 
 } 

 List<Opportunity> oppList = [ 
         select 
             id, 
             (select id, WhatID, IsClosed from Tasks), 
              Open_Tasks__c,Total_Tasks__c 
         from 
             Opportunity 
         where 
             id in :taskIds]; 

 for (Opportunity opp : oppList) { 
     Integer count = 0; 
     for(Task tsk : opp.Tasks) 
      { 
        if(tsk.WhatId != null  && !tsk.IsClosed)  
        count += 1; 
      }   

     opp.Total_Tasks__c = opp.Tasks.size(); 
     opp.Open_Tasks__c = count; 
 } 
 update oppList;     

 }

Please help me how can I get rid of this issue.

Answer 1

Earlier I have faced this find of issue, though in our org one Account could have more than 2000 activities.

I have seen that if I use inner query (select id, WhatID, IsClosed from Tasks) inside the Opportunity query, it gives the limit very early with almost half of its records.

The steps I have followed:

1. Use of separate aggregate queries to get the open task count and closed task count.

2.Then update those count into the opportunity record. You do not need to perform the SOQL query on Opportunity for that update.

Use this method createObject method which will give you an instance of the object based on the Ids.Call this method inside the for loop and later update the Opportunity. This way you can save at-least 1 SOQL query to retrieve Opportunity field info.

private sObject createObject(String typeName, Id objId) {
Schema.SObjectType targetType = Schema.getGlobalDescribe().get(typeName);
    if (targetType == null) {
    // throw an exception
    }
    // Instantiate an sObject with the type passed in as an argument
    //  at run time.
    return targetType.newSObject(objId);
}
//code snippet to update Opportunity records.
sObject[] sobjects = new list<sobject>(); 

for (String str: taskIds){
sObject obj = createObject('Opportunity',Id.valueOf(str));
obj.put('Open_Tasks__c', <retrive the count from query>);
obj.put('Total_Tasks__c', <retrive the count from query>);
sobjects.add(obj); }       
update sobjects; 

Also suggest you to create Trigger Helper class rather than putting everything inside trigger. Let me know if it helps!