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

This exception is talking about an “aggregate query” not an “aggregate function.” I had never heard the term “aggregate query” before, and I suspect you haven’t either. After hunting around, I found the relevant Salesforce documentation – Salesforce Apex Developer Guide: SOQL For Loops.

The documentation explains that Salesforce might throw the System.QueryException when a query has a sub-query (this is the “aggregate query”), and our sub-query returns more than 200 child records.

For example, if we have an Account with more than 200 AccountTeamMembers, we might get the exception when we run the following code with a query on Account with a sub-query for child AccountTeamMembers. 

Code that could throw an exception if there are more than 200 child records:

List<Account> accounts = [Select Id, (Select Id, AccountId , UserId From AccountTeamMembers) , (Select Id , OwnerId, AccountId from Opportunities) From Account]; 
for( Account acc : accounts )
{ 
   // Either of the following lines may throw the QueryException exception 
   // This is the 'direct assignment'
   List<AccountTeamMember> lstAccountTeamMembers = acc.AccountTeamMembers;
   
   // calling size() relies on the acc.AccountTeamMembers list being available
   Integer count = acc.AccountTeamMembers.size();
}

The way I conceptualize this is that the Salesforce database has not returned the complete list of child AccountTeamMembers. So when I try to assign the child AccountTeamMembersto the ‘lstAccountTeamMembers ’ list, the child list is incomplete. Salesforce lets you know the child list is incomplete by throwing the QueryException.

Here’s the fix:
Code that runs no matter how many child records:

// Note - we are using a 'SOQL for loop' here
for( Account acc :  [Select Id, (Select Id, AccountId , UserId From AccountTeamMembers) , (Select Id , OwnerId, AccountId from Opportunities) From Account]; 
)
{
   List<AccountTeamMembers> lstAccountTeamMembers;
   try
   {
      lstAccountTeamMembers= acc.AccountTeamMembers;
   }
   catch( QueryException e )
   {
      lstAccountTeamMembers= new List<Contact>();
      // Here's the 'FOR loop' the exception message says we should use
      // Within the outer SOQL for loop, this for loop can access the 
      // complete list of child records
      for( AccountTeamMember oatm : acc.AccountTeamMembers)
      {
         lstAccountTeamMembers.add( oatm );
      }
   }
 
   // Now we can use the 'AccountTeamMembers' List however we want.  Yay!
   System.debug( acc.Name + ' : ' + lstAccountTeamMembers.size() );
}

Using the try-catch as above is an Apex best practice. It will ensure our Apex runs efficiently, even when our sub-queries could possibly return more than 200 child records.