Limits for inline Query

Question

I have an account record having name 'POC', This account has 5000 contacts.

Now I am querying in Execute anonymous

1.

List<Account> accounts = [Select Id, Name, (Select  Id, Name From Contacts limit 5000) From Account Where Name= 'POC'];
System.debug('Contact Size::' + accounts[0].Contacts.size());

I got result in debug Contact Size::5000

Now I am modifying query(Adding Description field)

2.

  List<Account> accounts = [Select Id, Name, (Select  Id, Name, Description From Contacts limit 5000) From Account Where Name= 'POC'];
            System.debug('Contact Size::' + accounts[0].Contacts.size());

Getting an error :

FATAL_ERROR|System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

This query is working upto 999 contacts.

Answer 1

Just like the error says, it cannot directly assign the results of the query to the list, use a For loop

List<Account> accounts = [Select Id, Name From Account Where Name= 'POC'];
Map<ID,Contact[]> accountToContact = New Map<ID,Contact[]>();

for(Contact c :  [Select  Id, Name, Description, AccountId From Contacts Where AccountID IN :accounts]){

    Contact[] contactList = accountsToContact.get(c.AccountID);
    if(contactList == null) contactList = New Contact[]{};
    contactList.add(c);

    contactMap.put(c.AccountId,contactList);

}

Note, the getting of the list from the map has been stated in previous questions to be ideal vs constantly checking map keys etc. Better way to use Map of List

Also, this is a possible duplicate of: SOQL: How to handle relationship query with more than 50,000 results which may be a good read for you as well

Answer 2

You can use a chunked for loop; this is necessary because of the QueryLocator such a query generates:

for(Account aRecord: [SELECT (SELECT Id FROM Contacts) FROM Account) {
    Integer total = 0;
    for(Contact[] cRecords: aRecord.Contacts) {
        total += cRecords.size();
    }
    System.debug('Total contacts on account '+aRecord.Id+': '+total);
}

If all you really want are numbers, you can also use an AggregateResult:

for(AggregateResult ar:[SELECT AccountId Id, COUNT(Id) Total FROM Contact GROUP BY AccountId]) {
    System.debug(ar.get('Id')+' has '+ar.get('Total')+' contact(s).');
}