Apex Code Development You need to sign in to do that
Don't have an account?
Juan 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.
"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.
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.
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.
if possible, can you share your query? will give a try.
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();
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!
Thank you for the help