3

So the end result I'm hoping to achieve is to do a Left Anti-Join in SOQL without using Roll-Up Summary fields or Apex. Here's the scenario:

We have 2 custom objects, say Property__c and Connected_Device__c.

Connected_Device__c has the 2 following fields:

  • A lookup field to Property__c
  • a picklist field named Type__c with the picklist values: 'Hub', 'Thermostat', 'Garage'

meaning our property records will have a related list of connected devices. Here's the scenario:

  • Property A has 3 connected devices, one for each of the different Type__c values.

  • Property B has 2 connected devices, one for Thermostat and one for Garage.

If I wanted to query the properties that had at least 1 connected Hub devices, I could use the query:

SELECT Property__c, COUNT(Id) hubs
FROM Connected_Device__c
WHERE Type__c = 'Hub' 
GROUP BY Property__c
HAVING COUNT(Id) >= 1

But what if I wanted to find all the properties that have 0 connected Hub devices but that still have other linked connected devices (i.e. Property B)?

I wouldn't be able to build a query on the Connected_Device__c records that have Type__c = 'Hub', since the query result would always be empty.

And using a NOT() statement (i.e. (NOT Type__c = 'Hub')) would result in both property records being returned, since both have Thermostat and Garage connected devices.


I know that ultimately I could build a simpler SOQL query and then use logic in Apex to to get the result I want, or have Roll-Up Summaries on the object that count the number of Hub and non-Hub connected devices, but this is more of an exercise in determining if this is possible with SOQL alone.

Let me know if I need to add more to the question/clarify anything!

2
  • 2
    The "community" tag refers to a specific Salesforce feature (Salesforce communities, now called "experience cloud", for portals/sites a company would allow its non-Salesforce user customers to use), so I've removed that tag.
    – Derek F
    Nov 18, 2021 at 17:00
  • Haha for some reason I interpreted it as a "question for the community". Thanks for the edit @Derek F! Nov 19, 2021 at 16:14

1 Answer 1

4

It'd be something like:

SELECT Id
FROM Property__c
WHERE Id NOT IN (SELECT Property__c FROM Connected_Device___c WHERE Type__c = 'Hub')
  AND Id IN (SELECT Property__c FROM Connected_Device__c)

Note that you must use AND, as OR isn't supported. The Id IN and Id NOT IN syntaxes are incredibly useful for finding parent records that meet certain child criteria.

1
  • Awesome, thank you @sfdcfox. I appreciate the help on this! Nov 19, 2021 at 16:12

You must log in to answer this question.

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