1

I am checking the governor limits of SOQL queries and just want to confirm few things:

  1. When we have more than 200 records related to 1 account I am getting exception as : System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop. Please check below code:
for(Account acc:[Select id,(select id from contacts) from account where id = '0015g000003EgfK']){   
        System.debug(acc.contacts.size());
    }

why .size function is giving issue?

  1. SOQL query can query upto 50,000 records but what is the limit of child query records (Contacts records) that can be returned?
  2. If there is a limit of the rows return on child query will it be depending upon the account id like with 1 account only 200 or 300 contacts can be returned?
integer i = 0;
for(Account acc:[Select id,(select id from contacts) from account where id = '0015g000003EgfK']){   
    for(contact con: acc.contacts){
      i = i+1;
    }
}
system.debug('Total Contacts:'+i);
system.debug('Total Aggregate Queries:'+limits.getAggregateQueries());
system.debug('Total Query Rows:'+limits.getQueryRows());
system.debug('Limit query rows:'+limits.getLimitQueryRows());
  1. What limits.getQueryRows() should be returning? Will it be total count of contacts + account records or just total count of account records? Somehow for me its return 13 result while contacts counted by I variable is 211 and only 1 account is present.

  2. If I need to find out Average closing time for all cases related to account it, what could be the approach here as each account can have more than 50,000 cases so I cant use aggregate Result sum function while grouping it by accountid. I am looking for a trigger based approach rather than batch.


@derekF, Let me try to summarize and ask a few more detail on what you just posted above.

  1. if you have more child records than can be returned in a single chunk, you won't be able to use .size() --- Correct, I just tried in my org, although the solution I used is different. Below works for me, here total contacts are 2132 related to one account.
List<Account> accList = [Select id,(select id from contacts) from account where id = '0015g000003EgfK'];
system.debug('accList size' + accList[0].contacts.size());
    
for(Account acc:accList){   
  system.debug('Acc related Contacts size' + acc.contacts.size());
    for(contact con: acc.contacts){        i = i+1;
    }
}
  1. The 50k limit for query rows returned is for the entire transaction. There is no separate limit for parent-child subqueries --- I tried same code now in full copy sandbox and below are the results. Why the total query rows still showing 30,000?

enter image description here

  1. Rows queried as part of Semi-Joins and Anti-Joins do not count towards the query row limit. --- The joint query still be enforcing 50,000 rows governor limit right?

  2. There is no strict limit on the number of child rows that a parent-child subquery can return (outside of the overall 50k row governor limit that we're subject to) --- I know it will surely index skew issues when you have that much records. I was testing how many child records we can get when we do parent to child query. Coming to your answer, does that means there no limit on the number returned for contacts in parent to child query mentioned above right? 50,000 will only be limit for accounts?

2
  • Generally speaking, you should stick to one question per post.
    – Derek F
    Jul 7 at 13:17
  • @DerekF I understand the questions seems to be big enough, but all the questions are related to soql child query I was doing for point 5. Now, while digging down I wrote that snippet code attached to dig more in it and first 4 que are interrelated. I felt the that will help the person who can help me out here.
    – Raja Ray
    Jul 7 at 13:20
3

To aid in letting Salesforce manage resources in their very multi-tenant environment, they made it so that queries return data in chunks (there's some evidence that the chunk size is 200 like it is for triggers, but the exact number isn't documented anywhere that I am aware of).

When you end up having a query that returns more than one chunk of records, Salesforce performs an internal call to queryMore. This works just fine in most occasions but, for reasons we aren't told about, it's an issue when trying to fetch results of a parent-child subquery.

Generally speaking, you can't get the size of a list without first having the entire list in memory. So if you have more child records than can be returned in a single chunk, you won't be able to use .size(). If I were to speculate, I'd imagine that the reason why accessing any part of the child list is prohibited here is because it would be possible to write code that could attempt to access data not yet loaded. It'd be a security and support nightmare.

The solution is to use a nested for loop to access child records one at a time (and put them into a collection)

List<Opportunity> relatedOpps = new List<Opportunity>();
Map<Id, List<Opportunity>> oppsListByAccountId = new Map<Id, List<Opportunity>>();
for(Account acct :[some query here]){
    for(Opportunity opp :acct.Opportunities){
        relatedOpps.add(opp);

        // if you want to keep the association between the two objects, use a map
        if(!oppsListByAccountId.containsKey(acct.Id)){
            oppsListByAccountId.put(acct.Id, new List<Opportunity>());
        }

        oppsListByAccountId.get(acct.Id).add(opp);
    }
}

// Now you can check the size
system.debug(relatedOpps.size());

// getting the count per parent record would look like this
for(Id acctId :oppsListByAccountId.keySet()){
   system.debug(oppsListByAccountId.get(acctId).size());
}

The 50k limit for query rows returned is for the entire transaction. There is no separate limit for parent-child subqueries. In your case, it should be #Accounts + #Contacts. The best explanation I have to offer for why you're seeing getQueryRows() return 13 is that you're probably not looking at the correct part of the execution log.

Rows queried as part of Semi-Joins and Anti-Joins do not count towards the query row limit.

There is no strict limit on the number of child rows that a parent-child subquery can return (outside of the overall 50k row governor limit that we're subject to), but if you do have such a large number of child records related to a single parent, you'd likely run into issues with index skew (which can in turn cause issues with query selectivity). Using the nested for loop approach is the safe way to be able to access all of the child records.

5
  • 1
    SOQL query results Maximum rows returned 2,000 results total (API version 28.0 and later), unless you specify custom limits in the query. Previous API versions return 200 results. (regarding your first statement about SOQL chunk size). developer.salesforce.com/docs/… Jul 7 at 13:39
  • "One question per question" is a general rule on stackexchange sites. My answer covers the first 4 questions that you asked (which would probably be just specific enough to not be closed as "too broad" if reformulated). The fifth question you asked, however, is completely different and should be a separate question.
    – Derek F
    Jul 7 at 13:39
  • @MariiaIllarionova Does that means a for in loop can return a batch of 2000 record at a time?
    – Raja Ray
    Jul 7 at 16:12
  • @DerekF I am going a post my comment below, have a look.
    – Raja Ray
    Jul 7 at 16:15
  • @DerekF Can you please review below post. On comments it was not allowing me to have that much chracters.
    – Raja Ray
    Jul 7 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.