8

Is it possible to check for a the size of a related list in a SOQL query? I am trying to find all Accounts that have no Contacts over the default Contact->Account Lookup.

3 Answers 3

7

This will give you all accounts with no Contacts

list<Account> accs = [Select Id, Name, yourOtherFields 
                      From Account 
                      Where Id Not In (Select AccountId from Contact)];
3
  • That'll work, although you'll want to be careful about query row limits, since this involves querying ever contact in the org. Nov 7, 2013 at 23:59
  • 1
    You are quite right. I was more just showing him how the query would be constructed for that scenario. Depending on what he needs the data for, I would normally suggest Batch apex, assuming he needed to perform some actions on the data. You are right though, this is not exactly best practice as it would break over 50k records. Nov 8, 2013 at 0:27
  • You can fix that problem by using the "group by" clause in the subquery: (select accountId from Contact group by accountId). The number of records returned then will be less or equal to the number of Accounts in the system. Jul 14, 2014 at 12:59
2

No, it's not possible to filter a SOQL query on the number of related objects. Ideally we could do something like this

SELECT Id FROM Account WHERE COUNT(Contacts__r) = 0

As @SF_Ninja pointed out you can query all contacts in the org and then query all accounts that aren't related to them. However that'll be difficult to scale for medium to large orgs (you only get 50K query rows at this time so anything over that and your broke).

If the child object had a master detail relationship with the parent it's easy to create a rollup summary field with a count of the child objects which you can then filter on. For non-master detail relationships you can code a trigger to do your own rollup, or use Rollup Helper to create one with points and clicks.

Of course, this is now easy to do with Salesforce reports using the new cross filter. Just create an account report and add a filter that it only returns accounts without contacts. Now that the new analytics API is out, you could in theory pull that into an Apex class with a big of legwork (requires parsing the resulting JSON, and dealign with the nuances of connecting to a salesforce webservice in APEX)

1
0

Is it possible to check for the size of a related list in a SOQL query?

Yes, by workaround, and only provided they have a Master-Detail relationship. This allows you to create a rollup summary field to count the related objects, which can then be used in a query.

I am trying to find all Accounts that have no Contacts over the default Contact->Account Lookup.

Unfortunately Contact has a regular ol' lookup relationship, so the above doesn't work for your particular case.

An alternative solution is to add an ordinary Decimal field to Accounts for counting the contacts, then use a trigger on Contacts to keep it up to date. This is a complicated solution though, and should be done only after careful consideration. (E.g. don't forget your undelete triggers!)

You must log in to answer this question.

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