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:
- 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
- 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;
Custom_Object__c.Key__c = Account.Key__c
is not allowed), so this would need to be broken into at least 2 separate queries. One of those queries would need to query all of the records on one of your two objects, which raises concerns of query selectivity. Can you please go into more detail about the overall problem you're trying to solve?Account
record when a new value ofKey__c
is encountered, or is that what you're creating theCase
for? Does aCustom_Object__c
record always haveKey__c
populated? Is aCustom_Object__c
record always associated to anAccount
, or is it only associated to anAccount
when there is anAccount
with a matching value ofKey__c
? IfCustom_Object__c
is only associated to anAccount
whenKey__c
is the same, then a query forCustom_Object__c
records that aren't associated to anyAccount
should work (and is much easier to write).