Select where not exists / count(otherRecord) = 0

Question

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?

Answer 1

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 your Custom_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 have Key__c populated
• Custom_Object__c records are automatically associated to an Account only if there is an existing Account that has a matching value of Key__c
• If there isn't already an Account record with a matching Key__c value, it isn't possible for you to create a new Account at the time when new Custom_Object__c records are inserted
• This isn't a problem of associating Custom_Object__c records to Account records once the Account has Key__c populated

Your goals are:

1. Send an email to alert people when Custom_Object__c records are inserted, and there isn't an Account that has a matching Key__c value
2. Do some form of notification when an Account, that previously didn't have any related Custom_Object__c records, changes to have at least one Custom_Object__c related to it

We 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 != and null.

Obtaining the collection of Custom_Object__c records that don't currently match with any Account 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).

// I'm assuming that this will be part of a trigger
// The general process we'll follow here is:
// 1 - Iterate over the incoming Custom_Object__c records, and store them in a
//   Map<String, List<Custom_Object__c>>.
//   The key of the map will be values of the Key__c field
// 2 - Use the map's keyset to query for Accounts with matching values of Key__c
// 3 - Iterate over the results of the query, and store the Key__c values in a separate set
// 4 - Use methods of the Set class to arrive at the collection of incoming Custom_Object__c
//   records where there isn't an Account record with a matching Key__c

Map<String, List<Custom_Object__c>> keysToCustomObjRecs = new Map<String, List<Custom_Object__c>>();

for(Custom_Object__c custObj :trigger.new){
    // This if block only concerns itself with creating a new List to put Custom_Object__c
    //   records into when we encounter a new value of Key__c.
    // Adding records to that List would be common to the inside and outside
    //   of the if block, so I just add to the list outside of the if block to keep things
    //   DRY.
    if(!keysToCustomObjRecs.containsKey(custObj.Key__c)){
        keysToCustomObjRecs.put(custObj.Key__c, new List<Custom_Object__c>());
    }

    keysToCustomObjRecs.get(custObj.Key__c).add(custObj);
}

Set<String> accountKeysSet = new Set<String>();

for(Account acct: [SELECT Id, Key__c FROM Account WHERE Key__c IN :keysToCustomObjRecs.keySet()]){
    accountKeysSet.add(acct.Key__c);
}

// We can use set.removeAll on a map's keySet to effectively remove Custom_Object__c
//   records from the map that we know will match to an Account
keysToCustomObjRecs.keySet().removeAll(accountKeysSet);

List<Custom_Object__c> custObjsWithNoMatchingAccount = new List<Custom_Object__c>();

// Add all of our remaining custom object lists from our map into the aggregate list
for(List<Custom_Object__c> custObjList : keysToCustomObjRecs.values()){
    custObjsWithNoMatchingAccount.addAll(custObjList);
}

Your second goal is a bit harder. To avoid needing to query all of the Custom_Object__c records to figure out which Accounts don't have any related records, we'd want to use a rollup summary field. Since the relationship between Custom_Object__c and Account 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.

// I'll assume that you have a collection of Account Ids.
// As long as you meet that requirement, this code could be placed in either an Account
//   trigger or a Custom_Object__c trigger.
// I also assume that the API name of your lookup field to Account is 'Account__c'.
List<Account> accountsToUpdate = new List<Account>();
for(AggregateResult result :[SELECT Account__c, COUNT(Id) relatedRecords FROM Custom_Object__c WHERE Account__c IN :accountIds GROUP BY Account__c]){
    // We can use the SObject constructor to set the Id field.
    // This allows us to update an existing record without querying for it first
    accountsToUpdate.add(new Account(
        // using .get() is the only way to retrieve fields from an AggregateResult.
        // Doing so will return a plain Object, so we need to type-cast
        Id = (Id)result.get('AccountId'),
        Number_of_Custom_Objects__c = (Double)result.get('relatedRecords')
    );
}

// After this DML update, your Accounts will have an updated count of how many related
// Custom_Object__c records they have.
update accountsToUpdate;

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 which Accounts have 0 related records before updating the related record count. That's a pretty simple query.

[SELECT Id FROM Account WHERE Number_of_Custom_Objects__c = 0]

After your count update, you'll use another simple query to figure out which Accounts just got their first related records.

// Using != is bad for performance, and bad for selectivity.
// So, instead of Number_of_Custom_Objects__c != 0, we can use Number_of_Custom_Objects__c > 0, 
//   which should give us the same end result.
[SELECT Id FROM Account WHERE Number_of_Custom_Objects__c > 0 AND Id IN :<collection of Ids from the previous query>]

The one remaining piece of the puzzle is to calculate the count of related Custom_Object__c records on existing Accounts. The rollup code above will calculate this for you from this point forward, but you'll need to update your existing Accounts 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).

// You should adjust the date literal to match whatever time it is that you started
//   running this anonymous apex for the first time.
// This will prevent us from updating the same accounts over and over again.
// You might also need to adjust the LIMIT 5000 (either upwards or downwards) to perform
//   as many updates at once as you can (the maximum number for LIMIT will be 10000).
// For the inner query, the object name typically changes to the plural 
//   (Custom_Object -> Custom_Objects), and the __c becomes __r.
// Re-run this in anonymous apex until the debug log shows 0 query rows returned.
List<Account> accountsToUpdate = [SELECT Id, (SELECT Id FROM Custom_Objects__r) FROM Account WHERE LastModifiedDate < '2017-04-07T13:25:00.000Z' LIMIT 5000];

for(Account acct :accountsToUpdate){
    acct.Number_of_Custom_Objects__c = acct.Custom_Objects__r.size();
}

update accountsToUpdate;