I have a custom object with a custom field called Key__c and I have created the same Key__c field in the Account object. The Custom Object has a lookup relationship towards the account. I would need to perform the following query:
Find all the Account objects for which there's no record of type
custom object with field key_c. (SELECT Name from Account where not
exists (Select 1 from Custom_Object where Custom_Object.Key__c =
Account.Key__c)
How do you model this in SOQL?
Best Answer
The short answer is that there is no good translation from your target query into SOQL. SOQL does not allow comparing a field to another field. There is a way to do something similar, but it would involve querying either all of your
Accounts
or all of yourCustom_Object__c
records (and Salesforce strongly discourages querying all records of any object).Summing up the information from the comments and our chat log:
Custom_Object__c
records always haveKey__c
populatedCustom_Object__c
records are automatically associated to anAccount
only if there is an existingAccount
that has a matching value ofKey__c
Account
record with a matchingKey__c
value, it isn't possible for you to create a newAccount
at the time when newCustom_Object__c
records are insertedCustom_Object__c
records toAccount
records once theAccount
hasKey__c
populatedYour goals are:
Custom_Object__c
records are inserted, and there isn't anAccount
that has a matchingKey__c
valueAccount
, that previously didn't have any relatedCustom_Object__c
records, changes to have at least oneCustom_Object__c
related to itWe want to do this in a way that uses so-called 'selective' queries. In a nutshell that means that we want to write queries that return as few results as possible, use as many indices as possible, and avoid using
!=
andnull
.Obtaining the collection of
Custom_Object__c
records that don't currently match with anyAccount
is a fairly straight-forward process. I don't think there's a way to make a query do all the work and still have it be selective, so we'll need to do some work outside of the query (in Apex).Your second goal is a bit harder. To avoid needing to query all of the
Custom_Object__c
records to figure out whichAccounts
don't have any related records, we'd want to use a rollup summary field. Since the relationship betweenCustom_Object__c
andAccount
is a lookup relationship, we can't use Salesforce-provided rollup-summary fields.You could use Andrew Fawcett's Declarative lookup rollup summary tool, but that creates its own trigger. When one object has more than one trigger that fires on the same events (e.g.
after update
), Salesforce doesn't guarantee the order in which the triggers are executed (and the order of execution will be important to us). You could copy/paste the generated trigger code into your existing trigger, but that removes a big part of the functionality of the DLRS tool (that is, being able to change the rollup declaratively).Instead, we can just make our own rollup code (it ends up being pretty short) using a query and the
COUNT()
aggregate function.Given that your goal is to determine which
Accounts
had 0 related records, but now have more than 0, you'll want to figure out whichAccounts
have 0 related records before updating the related record count. That's a pretty simple query.After your count update, you'll use another simple query to figure out which
Accounts
just got their first related records.The one remaining piece of the puzzle is to calculate the count of related
Custom_Object__c
records on existingAccounts
. The rollup code above will calculate this for you from this point forward, but you'll need to update your existingAccounts
yourself.This can be done via anonymous apex, fairly simply, using a query on
Account
and a left outer join (a.k.a. a parent-child subquery).