Governor Limits related to SOQL in Apex code for Child Query

Question

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?

2. SOQL query can query upto 50,000 records but what is the limit of child query records (Contacts records) that can be returned?
3. 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());

4. 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.

5. 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;
    }
}

2. 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?

3. 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?

4. 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?

Answer 1

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.