Fetch Accounts having more than 3 contact records in it in a single query

Question

I want to Fetch all the accounts records(not the ids) that have 3 or more contacts present in a single query with contact records in it.

I didn't find any reference of these.

I had tried with some of the feature with that we can do but there I had to iterate through the collections. But I don't want that.

List<Account> acList = [SELECT Id, Name, (SELECT ID, Name FROM Contacts) FROM Accounts];

List<Account> selectedAccounts = new List<Account>();

for(Account ac: acList){
     if(ac.Contacts.size() > 3){
         selectAccounts.add(ac);
     }
}

But this solution I don't want ^^^

Neither do I want this select accountid from contact group by accountid having count(id) > 3

Can anyone please advice as how to get the records in any other way

Answer 1

You can't do this in a single query (and only a query) unless you've previously rolled up the number of Contacts to the Account (which would require a trigger and a query, since we can't use a standard rollup summary field on Account to summarize Contacts).

I'm afraid you're just going to have to "take your medicine" here.

If you have some way to limit the number of Accounts that you're trying to query (other than on the number of related Contacts), so that you can make a query selective, then the parent-child subquery + apex processing approach is the one I'd take.

If you don't have a way to limit the number of Accounts you're querying, then you should use the GROUP BY AccountId HAVING COUNT(Id) > 3 query to get a limited set of Ids and a second query to get the Account data you're actually interested in.