Get count of records from a child object which has a reference field to a parent object

Question

To put this simply. I have 2 Objects Let's call them SalesRep and Clients.

And the relation is a one-to-many relationship (ie: SalesRep(Parent) -> Clients(Child))

So obviously in Clients Object, it has a reference to its parent. Let's call that field SalesRep__r

And also in the Clients Object, there is a Boolean field called "Active". which is used to know if a client is an Active Client or not.

I need to query the list of SalesRep that "has 0 active Clients" OR "No clients at all" with SOQL

Answer 1

There's normally two ways to do this in SOQL, but using an aggregate query from the child to the parent won't work in this case because you're looking for zero OR zero active clients.

(The aggregate query would otherwise look something like [SELECT count(Id), SalesRep__r.Id FROM Client__c WHERE Active__c = true GROUP BY SalesRep__r.Id HAVING count(Id) > SOME_THRESHOLD]. But since you're looking for Sales Reps with a count of zero, you can't use that form as there aren't any Client__c records to join on).

The most likely way is to query from the parent to the child and filter the list in Apex. Something like:

for (SalesRep__c sr : [SELECT Id, (SELECT Id FROM Clients__c WHERE Active__c = true) FROM SalesRep__c]) {
    if (sr.Clients__c.size() == 0) {
        listOfClients.add(sr);
    }
}

If it's imperative to do it in SOQL alone, you might be able to get it to run with a subquery:

SELECT Id FROM SalesRep__c WHERE Id NOT IN (SELECT SalesRep__r.Id FROM Client__c WHERE Active__c = true GROUP BY SalesRep__r.Id)

You can also do this in a native Salesforce report with a cross filter, provided you have a "Sales Reps and Clients" custom report type that's configured to show Sales Reps with and without Clients.