function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rakesh ERakesh E 

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

Hi ,

 

i wrote a query which has an inner query in it to get the child records.

 

i will give an example with standard objects

 

Account acc = [select name,(select firtsname,lastname from Contact) from account limit 1];

 

i am using this "acc" varibale in VF page to display the contact records.

 

<apex:page>

<apex:repeat value="{!acc.contacts}" var="contact" >

 

<apex:outputtext value="{!contact.firstname}"/>

<apex:outputtext value="{!contact.lastname}"/>

</apex:repeat>

 

i have around 130 contacts, but iam getting below error message when i try to open the VF page to view the results.

 

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

 

please let me know if anyone faced this issue earlier. what is the limit of number of rows in aggregate query.

is ithis is the correct way to get this done.

 

actually in my query there are around 15 inner object queries. that is my parent object has 15 childs.

 

any help would be appreciated

 

thanks in advance

 

Regards,

Rakesh

 

 

 

marcobmarcob

Hi Rakesh,

i've experienced the same thing: query on an object with not a substantial amount of rows, but still that error. Didn't get the hang of it, so I changed my code from this construction:

 

for(myObject__c x: [select name, etc..., (select name, etc... from myChildObject__r) from myObject__c]){

    ....

}

 

into this new construction:

 

list<myObject__c> myList = [select name, etc..., (select name, etc... from myChildObject__r) from myObject__c]);

for(myObject x : myList) {

    .....

}

 

And that works.

Hope this helps!
Br, Marco
HariPHariP
This approach also gives an error if there are more records in child record.

I have similar situation, it failed for 500 records. I tried with limit 200 in sub query (myChildObject__r limit 200) and it worked. Not sure what is the real limitation on sub query records.

Hope this helps!
Hari
Abhay_ZodapeAbhay_Zodape
**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 acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) {
                List<Contact> contactList = acct.Contacts; // Causes an error
                Integer count = acct.Contacts.size(); // Causes an error
            }
            
            To avoid getting this exception, use a for loop to iterate over the child records, as follows.
            for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account WHERE Id IN ('<ID value>')]) {
                Integer count=0;
                for (Contact c : acct.Contacts) {
                    count++;
                }
            }    

Hope this helps.
Abhay Zodape
Rajesh Sharma 65Rajesh Sharma 65
Hi, is there any workaround for the above exception
Jose Daniel Angarita 14Jose Daniel Angarita 14
Wow, I can't believe this is a thing, is there any place in the documentation about this? This looks like a bug to me, and is been around for 6 years now?

The inner for loop fixes the issue, but wow.
SFDC 1904SFDC 1904
We are iterating over child records still getting query exception. As soon as child record number is more than 30 we get exception. 
Elkin CordobaElkin Cordoba
Check for Long Text Area in your query and remove them