How to query Account object with minimum number of children?

Question

There is a scenario where I need to query the Account records that has the associated contacts >= 100, Since accounts and contact are not Master Detail relation ship I could not create roll-up summary.

I tried a similar query from stack exchange from the previous Question however that did not worked.

Select id,(Select Id from Contacts)From Account Where Id IN (Select Account from Contact Where Name !='')

Answer 1

That can be a difficult query since Accounts and Contacts typically have a lot of records. There's a risk of running into the "Too many query rows" exception, but if you don't have too much data at this point, this query might work for you:

Apex:

list<AggregateResult> arlist = [
    SELECT COUNT(Id), AccountId
    FROM Contact 
    WHERE AccountId != NULL 
    GROUP BY AccountId
    HAVING COUNT(Id) >= 4
];

Rest api:

$ curl -H 'Authorization: Bearer <session-id>' \
  -H 'X-PrettyPrint: 1' \
  https://ap4.salesforce.com/services/data/v38.0/query?q=SELECT+COUNT%28Id%29,+AccountId+FROM+Contact+WHERE+AccountId+!=+NULL+GROUP+BY+AccountId+HAVING+COUNT%28Id%29+%3E=+4 

Answer 2

You can try following query:

SELECT Id, (SELECT Id FROM Contacts) FROM Account WHERE Id IN (SELECT AccountId FROM Contact)

What you have missed is having AccountId in outer join.

---

Update:

If you want to go by apex route, can use maps to display the data:

Map<Id, Integer> mapAccountWithContactCount = new Map<Id, Integer>();

for(Account objAccount: [SELECT Id, (SELECT Id FROM Contacts) 
    FROM Account WHERE 
    Id IN (SELECT AccountId FROM Contact)]) {
    mapAccountWithContactCount.put(objAccount.Id, objAccount.Contacts.size());
}

Answer 3

Due to SOQL governor limits you'll have a hard time doing these queries once you have a lot of data. A workaround would be to pre-calculate the number of Contacts on each Account, store that value on a custom field, and then use that field in your query.

Example:

SELECT ID FROM Account WHERE Num_Contacts__c > 100

The values can be pre-calculated with a trigger, for real-time values, or with scheduled batch jobs if real-time isn't necessary.