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
    Commented Mar 21, 2017 at 7:10
  • 1
    @martin I to get the list of accounts that has at least 4 related contacts associated to it. Commented Mar 21, 2017 at 7:19
  • 3
    Man, sometimes it feels like you are trying to be as vague as you possibly can.
    – Adrian Larson
    Commented Mar 21, 2017 at 7:54

3 Answers 3

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 Commented Mar 21, 2017 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
    Commented Mar 21, 2017 at 8:02
  • Another option might be to move the query to a batch job. Commented Mar 21, 2017 at 12:02
  • 1
    @IllusiveBrian aggregate queries don't work very well in batch apex.
    – martin
    Commented Mar 21, 2017 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
    Commented Mar 21, 2017 at 20:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .