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

Question

List<sObject> activityRows = obj.getSObjects(activityType);

Here activitytype is nothing but openactivity and closedactivity.

I am wondering why am I getting this error. However I am not using any of the aggregate functions. After research, I found that it is because of the inner query I am using. But this error is getting if there are 500+ task records for the account.

Here is the query I am using

SELECT Id, Name, ( SELECT Id, AccountId, AlternateDetailId, ActivityDate, ActivitySubtype, ActivityType, Description, EndDateTime, IsTask, OwnerId, Owner.Name, PrimaryAccountId, PrimaryWho.Name, PrimaryWhoId, ReminderDateTime, StartDateTime, Status, Subject, WhatId, WhoId, Type__c FROM OpenActivities ORDER BY ActivityDate ASC NULLS FIRST, LastModifiedDate DESC LIMIT 500 ),  ( SELECT Id, AccountId, AlternateDetailId, ActivityDate, ActivitySubtype, ActivityType, Description, EndDateTime, OwnerId, IsTask, Owner.Name, PrimaryAccountId, PrimaryWho.Name, PrimaryWhoId, ReminderDateTime, StartDateTime, Status, Subject, WhatId, WhoId, Type__c FROM ActivityHistories ORDER BY ActivityDate DESC NULLS FIRST, LastModifiedDate DESC LIMIT 500) from account where id='09845678'

sObject obj = Database.query(querystring)

Later I changed it to for loop like below

list<sObject> activityRows = new List<SObject>();
System.debug('activityType:1 '+activityType);
for(sObject objOpen: obj.getSObjects('OpenActivities')){

    activityRows.add(objOpen);
}

That I am getting this exception Invalid Querylocator

Note This exception only comes when there are 499+ records

I have been sitting with this for the past 1 day. Please share your idea it will be helpful for me.

Answer 1

The error Salesforce emits here uses some unfortunate wording. An "Aggregate" query here has nothing to do with aggregate functions (SUM, COUNT, MIN, etc...) or AggregateResult, it's just how Salesforce chose to refer to parent-child subqueries.

As a brief side note, there is a separate and lesser-known limit for these "aggregate" queries (we can have up to 300 of them in a single transaction).

Back to business, you get the "Aggregate query has too many rows for direct assignment" error when, well... there are "too many" child records.

To help manage heap space (and server resources too, I'd imagine), Salesforce can transparently perform a queryMore operation to fetch the next part of the query result set. That approach breaks down somewhat when dealing with records in a child subquery. You get that exception when a queryMore call is made to fetch additional child records. Even trying to get the number of child records with .size() can cause that exception to occur.

Like the exception suggests, the solution here is to access the child records via a loop. Example

List<Account> myAccounts = [SELECT Id, Name, (SELECT Id, Name FROM Opportunities) FROM Account LIMIT 100];

for(Account acct :myAccounts){
    system.debug(acct.Name);

    for(Opportunity opp :acct.Opportunities){
        system.debug(opp.Name);
    }
}

I haven't encountered that second exception (invalid queryLocator) before in this context. Perhaps it's due to the odd way you're dealing with the query (putting the result into a generic SObject instance).

You don't appear to need to work with generic SObjects here, or dynamic queries for that matter. I'd recommend not making your life any harder than it needs to be. Just use normal queries (use square braces, not database.query()) and keep everything appropriately typed.