+ Start a Discussion
Juan Merideño 4Juan Merideño 4 

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

I am trying to improve the query performance as much as possible by getting all the data in just one query. When I try to get related records, if there are more than 100 records, the system returns from next error:

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

The query works, but we can not iterate the elements nor even get the amount of elements returned in the sub-query.

There is no documentation talking about that limit. And it is really easy to surpass it, so I can never guarantee that any query is going to work always.

I would like to know if there is any workaround to avoid this error.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Juan,
Have you tried QueryLocatorIterator Class to iterate and process through records across limits.

Please refer,
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_class_database_querylocatoriterator.htm#apex_class_database_querylocatoriterator

Please mark it as solved if this answers your question.
 
Juan Merideño 4Juan Merideño 4
Hi Malni,

Thank you for your answer. Unfortunatelly, what I am trying to iterate are the records of a subquery and I cannot use the QueryLocator for this. I have tried it, though, but I could not make it work. I am still getting the same error.
Malni Chandrasekaran 2Malni Chandrasekaran 2
Juan, 
if possible, can you share your query? will give a try.
Juan Merideño 4Juan Merideño 4
My code would be something like this:

Account acc = [SELECT Id, Some_other_fields__c,
            (SELECT Id, Some_other_fields__c FROM RelatedAccounts1__r),
            (SELECT Id, Some_other_fields__c FROM RelatedAccounts2__r),
            (SELECT Id, Some_other_fields__c FROM Contacts),
            (SELECT Id FROM Opportunities)
                       FROM Account
                       WHERE Id ='001xxxxxxxxxxxx'];
integer i = 0;
for(Account a : acc.RelatedAccounts1__r){
    i = i + 1;
    system.debug(i + '-' + a.Id);
}

The loop returns an error after 100 records. I also get an error when I try to get the size:

acc.RelatedAccounts1__r.size();
Malni Chandrasekaran 2Malni Chandrasekaran 2
Juan,
Normally this error occurs when count reaches more than 200. Not sure why you are getting at the count of 100, may be because of multiple subqueries.
You may add Limit 100 at the end of the subquery to avoid this error or you can try batch apex too if you have not tried yet.
Is RelatedAccounts1__r object is of type Account ?
If not, try replacing
for(Account a : acc.RelatedAccounts1__r){
with
for(RelatedAccounts1__r a : acc.RelatedAccounts1__r){

Hope this helps!
Juan Merideño 4Juan Merideño 4
Both RelatedAccounts1__r and RelatedAccounts2__r are type Account. I guess it is not possible. A batch is not an option for us.

Thank you for the help