function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Bryan HuntBryan Hunt 

SOQL query, WHERE clause reference to related list size

I have seen threads with dozens of questions about this over the last 6-7 years, all with the answer of "can't be done".  But, I keep hoping that each new version of SFDC might include a way of accomplishing it, so I'll ask the question once again.

I would like to use SOQL to query a parent object based on whether it does or does not have any child records (whether MD or Lookup).

The perfect query would be something like: 
- SELECT Id FROM Account WHERE COUNT(Contacts__r) = 0
or
- SELECT Id FROM Account WHERE Contacts__r.size() = 0
or
- SELECT Id FROM Account WHERE Contacts__r = null

From what I have found in threads as late as 2013, this does not appear to be possible.

Is that still the case in the Summer 2014 release?

It would appear that (for my needs) the best way to execute this type of query would be to:
- SELECT Name, (SELECT name FROM Contacts) FROM Account
- Then programmatically check the size of the returned set of Contacts

Is this my best option?  I know that I could:
- SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact)
But the actual objects that I want to interogate are more that the 50k limit for this type of query.

Thanks.

Bryan Hunt
ShashForceShashForce
Hi,

I would suggest you to store the Contacts count in a custom field on Accounts. This update can be done using a trigger. You can then use this field in your WHERE clause.

Thanks,
Shashank
Ankit AroraAnkit Arora
@Bryan
Is it mandatory to use SOQL in a way you suggested? Is it possible to use aggregate queries? Like this :

AggregateResult[] groupedResults
  = [SELECT COUNT(ID) FROM Contact where Accountid = '00190000007NqKq'];

System.debug('groupedResults :::::::: ' + groupedResults) ;

Will that work? Else am afraid the answer is still the same :(
Bryan HuntBryan Hunt
Shashank, we use that technique for some other objects.  In this case, the need to check for child records tends to be for one-off situations so not appropriate to setup a count field.  Thanks for the suggestion.

Ankit, in this case we will be reading 300k+ parent records so we may need to just use the programmatical check to see if there are >0 child records.

I wonder how Salesforce does this?  When you pull up an Account record, the counts for every related object pop up immediately (in our case, 12 related objects).  I can hardly believe that they do 12 'select count()' queries just to bring up the single Account record?

Thanks.

Bryan Hunt
Mendy - KurantMendy - Kurant
@bryan
Check it out.

list<Account> accounts =  [SELECT Id FROM Account WHERE id NOT IN (select AccountId from Contact)];

It returns only accounts with no contacts.
Tim Osborn 5Tim Osborn 5
@Mendy-Kurant, my appologies, I was trying to thumbs up your comment, and my browser stopped working until I clicked thumbs down... Now it won't let me change it.