6

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 !='')
3
  • 3
    Your query is throwing an error because Account is not the name of a field on the Contact object, it should be AccountId: Select id,(Select Id from Contacts)From Account Where Id IN (Select AccountId from Contact Where Name !='')
    – martin
    Mar 21 '17 at 7:10
  • 1
    @martin I to get the list of accounts that has at least 4 related contacts associated to it. Mar 21 '17 at 7:19
  • 3
    Man, sometimes it feels like you are trying to be as vague as you possibly can.
    – Adrian Larson
    Mar 21 '17 at 7:54
6

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 
4
  • Do we have any other solution when it comes to huge list of Accounts. now We have around 3000 Accounts Mar 21 '17 at 7:53
  • If you have ~20 contacts per account, you might have a hard time calling this query directly through apex. You might have to reach out and do the query through an api if you find you are hitting the limit.
    – martin
    Mar 21 '17 at 8:02
  • Another option might be to move the query to a batch job. Mar 21 '17 at 12:02
  • 1
    @IllusiveBrian aggregate queries don't work very well in batch apex.
    – martin
    Mar 21 '17 at 12:17
5

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());
}
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.

1
  • 1
    DLRS is an easy choice for the rollup there. You get both trigger and batch without writing any code on your own.
    – Adrian Larson
    Mar 21 '17 at 20:56

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.