+ Start a Discussion
Ramesh VaratharajRamesh Varatharaj 

Aggregate query has too many rows for direct assignment, use FOR loop

I have a trigger to rollup the open tasks at lead and contact level. The trigger occasionally throws exception - caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop
Please suggest how to fix this issue

trigger CalculateOpenTasks on Task (after delete, after insert, after undelete, 
after update) {
// Declare the variables
public set<Id> LeadIDs = new Set<Id>();
public list<Lead> LeadsToUpdate = new List<Lead>();
public set<Id> ContactIDs = new Set<Id>();
public list<Contact> ContactsToUpdate = new List<Contact>();

// Build the list of Leads and Contacts to update
if(Trigger.isInsert || Trigger.isUnDelete || Trigger.isUpdate){
    for(Task t: Trigger.new){
        if(t.WhoId<>null){
            if(string.valueOf(t.WhoId).startsWith('00Q')&&t.IsClosed==False && t.Marketing__c==True)
            LeadIDs.add(t.WhoId);
            if(string.valueOf(t.WhoId).startsWith('003')&&t.IsClosed==False &&  t.Marketing__c==True)
            ContactIDs.add(t.WhoId);
        }
    }
}

if(Trigger.isDelete || Trigger.isUpdate){
    for(Task t: Trigger.old){
        if(t.WhoId<>null){
            if(string.valueOf(t.WhoId).startsWith('00Q')&&t.IsClosed==False &&  t.Marketing__c==True)
            LeadIDs.add(t.WhoId);
            if(string.valueOf(t.WhoId).startsWith('003')&&t.IsClosed==False &&  t.Marketing__c==True)
            ContactIDs.add(t.WhoId);
        }
    }
}

// Update the Leads

if(LeadIDs.size()>0){
for(Lead l: [Select l.Id, l.Open_Tasks__c,
(Select Id From Tasks where IsClosed = False AND Marketing__c=True)
From Lead l where Id in :LeadIDs])
LeadsToUpdate.add(new Lead(Id=l.Id, Open_Tasks__c = l.Tasks.size()));
update LeadsToUpdate;
}

// Update the Contacts

if(ContactIDs.size()>0){
for(Contact c: [Select c.Id, c.Open_Tasks__c,
(Select Id From Tasks where IsClosed = False AND Marketing__c=True )
From Contact c where Id in :ContactIDs])
ContactsToUpdate.add(new Contact(Id=c.Id, Open_Tasks__c = c.Tasks.size()));
update ContactsToUpdate;
}

}
James WooleyJames Wooley
Instead of looping direcly around the results from a soql query, try assigning the results to a variable first. For example, instead of:

for(Object x: [select name, ..., (select name, ... from ChildObject) from Object]){
    ....
}

use:

list<Object> myList = [select name, ..., (select name, ... from ChildObject) from Object];
for(Object x : myList) {
    .....
}
Mollie Bodensteiner 6Mollie Bodensteiner 6
Hey James, This solution isn't working for me. I am getting a duplicate variable for my list name. 

if(ContactIDs.size()>0){
list<Contact> ContactstoUpdate = [Select c.Id, c.Open_Activities__c,
(Select Id From Tasks where IsClosed = False AND Marketing__c=True)
From Contact c where Id in :ContactIDs];
    for(Contact c: ContactstoUpdate){
ContactsToUpdate.add(new Contact(Id=c.Id, Open_Activities__c = c.Tasks.size()));
update ContactsToUpdate;
}
}

Can you help me understand what  I did wrong?