System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop. This exception is sometimes thrown when accessing a large set of child records (200 or more) of a retrieved sObject inside the loop, or when getting the size of such a record set.

For example, the query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}

In order to avoid System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop, make sure the sub query is limited as follows.

for (Account acc : [SELECT Id, Name, (SELECT Id, Name FROM Contacts LIMIT 200) FROM Account WHERE Id IN ('<ID value>')]) { 
    
}

Leave a Reply