5

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.

  • 1
    Interesting. The documentation mentions a 1000 record limit...on external objects. Perhaps it applies for some reason to Contacts as well? Sounds like a bug, perhaps. – Adrian Larson Aug 29 '16 at 15:38
  • Are you perhaps using .size() on the resultant data: help.salesforce.com/… – Eric Aug 29 '16 at 15:54
  • I used .size() in first query and I am not getting any error in first case – Yogesh Gupta Aug 29 '16 at 15:57
  • It's a bug : help.salesforce.com/… – Avanish Kumar Aug 29 '16 at 16:28
  • @AvanishKumar It's not a bug, it's a feature. Returning too much data would cause you to exceed governor limits while processing the query. – sfdcfox Aug 30 '16 at 11:21
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

1

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).');
}
  • You are right but I just want to know limit, sometimes it working for 5000 and sometimes not working for 999(When including description field) @sfdcfox – Yogesh Gupta Aug 30 '16 at 11:03
  • @YogeshGupta The system will choose a limit for you based on the amount of data that can be returned. Using a long text field, for example, will reduce the maximum batch size for a query. There's no way to control what the limit is, or even know what the limit is, which is why you generally should use a for-loop. The logic for how records are queried are abstracted away using standard API mechanisms (QueryLocator/query/queryMore). Generally, if you just want a total, use an aggregate result, otherwise loop over your results. – sfdcfox Aug 30 '16 at 11:09
  • I agree with you, Can we query 49999 child contact and 1 parent account, if we are not exceeding the limit of data amount, we are just fetching Id – Yogesh Gupta Aug 30 '16 at 11:17
  • @YogeshGupta I don't think that would be possible without a for loop. The system will eventually choose to provide a QueryLocator if there's too many rows/too much estimated data. I would have thought that 2,000 was the maximum, the same as the API maximum batch size, but clearly your code demonstrates that this is not true. Regardless, I don't think it'd be possible to go over 10k rows as an absolute maximum. If I find something, I'll let you know. – sfdcfox Aug 30 '16 at 11:27

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.