+ Start a Discussion
Jeffrey ZhangJeffrey Zhang 

Getting too many SOQL queries 101 error with my trigger.

trigger ba on Task (after update, after insert) {
    
    GetMaxActivityDate ad= new GetMaxActivityDate();
    
    
    
    Set<ID> ids = Trigger.newMap.keySet();
    
    List<Task> tk = [select id, whoId,OwnerId from Task where id = :ids limit 1000];
    System.debug('b tasks:'+ tk+ ' size:'+tk.size());
    List<ID> idzz = new List<ID>{};
        
        for(Task a: tk)
    {
        idzz.add(a.whoId);
    }
        
    
    List<Lead> ldz = [select name,id, Sales_Last_Activity_Date__c, Sales_First_Activity_Date__c, OwnerId, IsConverted from Lead where id = :idzz limit 1000];
    
    System.debug('b ids:'+ idzz+'. leads:' + ldz + ' size:' + ldz.size());
    
    List<Lead> updateld = new List<Lead>{};
    
    
    for(Lead a : ldz)
    {
        
        
    
    
    
    
    
                    String ldd = a.id;
        
        if(ldd!=null)
        {
           
        String lds = ldd.subString(0,3);
        
        System.debug('lds: '+ lds);
        
        if(lds.equals('00Q'))
        {
            
             List<aggregateResult> dates = new List <aggregateResult>();
        

        
        
        
       // Lead ld = [select name,id, Sales_Last_Activity_Date__c, Sales_First_Activity_Date__c, IsConverted from Lead where id = :a.WhoId limit 1];
        
        
        
        if(a!=null)
        {
            System.debug('b ' + lds);
            
			Set<ID> taskset = new Set<ID>();
			List<ID>result = new List<ID>();            
            taskset.addAll(idzz);
            result.addAll(taskset);
            System.debug('taskset:'+ taskset.size()+' result:'+result.size());
            
            
            for(ID b:result)
            {
            
            if(b == a.Id)
                {
                     dates = ad.getMaxActivity(b);
                    System.debug('b triggered:  max:'+ (Date)dates[0].get('myMax') + ' min: '+ (Date)dates[0].get('myMin') + 'last activity date:'+ a.Sales_Last_Activity_Date__c + ' first activity date ' +  a.Sales_First_Activity_Date__c+  ' isconverted:'+ a.IsConverted );
                        System.debug('b triggered: lead:'+ a);
                    if(a.IsConverted == false && (dates[0].get('myMax')!=null) && dates[0].get('myMin')!=null && (a.Sales_Last_Activity_Date__c != (Date)dates[0].get('myMax')||a.Sales_First_Activity_Date__c != (Date)dates[0].get('myMin')))
                    {
                        System.debug('Activity_date updated');
                        
                        a.Sales_Last_Activity_Date__c = (Date)dates[0].get('myMax');
                        a.Sales_First_Activity_Date__c = (Date)dates[0].get('myMin');
                        
                        updateld.add(a);
                    }
                    
                    
                }
                
            }
            
        }
        }

        }
    }
    
    update updateld;
    
    
    
    
    
    for(Task a: Trigger.New)
    {

      
        
        
        
    }

}

  public  List<aggregateResult> getMaxActivity(ID who)
    {
        List<aggregateResult> maxDates = [Select Max(Sales_Activity_Date__c) myMax, Min(Sales_Activity_Date__c) myMin from Task 
         where whoid = :who and status = 'Completed'];
            System.debug('Max is:  ' + maxDates[0].get('myMax') + 'min '+maxDates[0].get('myMin'));  
     
        return maxDates;
    }
I was getting this governor limit error in the past, and I rewrote most of the trigger to hopefully fix that, yet I still occasionally see this error. And from personally inserting a bunch of data, i can't seem to reproduce so its having a hard time debug. 

I'm thinking its due to getMaxActivity though because techinically its a select in a for loop? I'm not sure how else logically I can address this though.

thanks!
 
Best Answer chosen by Jeffrey Zhang
Morgan MarcheseMorgan Marchese

Your code is incredibly hard to read, due in part to its formatting and in other part due to the strange variables (like idzz) that you have assigned to your Sets and Lists etc.

However, the answer to your issue is indeed that you are doing a query in a loop (dates = ad.getMaxActivity(b);) - This line will fail at 101 times through the loop.

The best practice when you need to get a List of objects that relate to a specific record in a loop is to first put a map together before your loop, in this case probably a Map<Id,List<AggregateResult>>, where Id is your loop variable and List<AggregateResult> is your dates, and then instead of doing dates = ad.getMaxActivity(b); you would do something like...

List<AggregateResult> dates = IdToDatesMap.get(b);

The map will already be populated with all of the results from your (single) SOQL query, and then be populated out of the map into the dates variable for you to with as you wish

All Answers

Morgan MarcheseMorgan Marchese

Your code is incredibly hard to read, due in part to its formatting and in other part due to the strange variables (like idzz) that you have assigned to your Sets and Lists etc.

However, the answer to your issue is indeed that you are doing a query in a loop (dates = ad.getMaxActivity(b);) - This line will fail at 101 times through the loop.

The best practice when you need to get a List of objects that relate to a specific record in a loop is to first put a map together before your loop, in this case probably a Map<Id,List<AggregateResult>>, where Id is your loop variable and List<AggregateResult> is your dates, and then instead of doing dates = ad.getMaxActivity(b); you would do something like...

List<AggregateResult> dates = IdToDatesMap.get(b);

The map will already be populated with all of the results from your (single) SOQL query, and then be populated out of the map into the dates variable for you to with as you wish

This was selected as the best answer
Jeffrey ZhangJeffrey Zhang
Makes sense will try that. 
d.tejdeep@nicomatic.ind.tejdeep@nicomatic.in
Hi can please your edited code it will be very helpfull for me .I am same issue like