It is common to have collections of records to process, especially when considering triggers (though there are many other places where collections of records may require processing together). When that processing needs to involve other records, identified by some combination of values in the input records, you need to query those other records in a bulkified way, to avoid SOQL query limit problems, then organize them in a way that reduces the over-all loop-based processing in order to avoid CPU limit problems.
There are different scenarios where you need to do this, that can use different implementation patterns to resolve them. These scenarios are covered below.
I just need data from directly related records and I'm querying the input records
In this scenario, relationships are "parent to child": the input records have Lookup or Master-Detail relationship fields to the related records.
If you're in control of the querying of the input records, you can incorporate querying of the related record details directly as part of that initial query, then simply traverse the relationship in order to get to the details you need. For example, you are querying Opportunities and you need the related Accounts' Owner IDs so you can set the Opportunities' Owner IDs:
// You have some condition for loading the Opportunities. Here we assume you have some
// list of IDs but adapt this to your use case as needed
List<Id> oppIds = …;
List<Opportunity> opps = new List<Opportunity>();
// Process the Opportunities, making sure to query their related Accounts as we go
for (Opportunity opp : [SELECT Id, OwnerId, Account.OwnerId FROM Opportunity
WHERE Id IN :oppIds]) {
// We only want to update the Opportunity if we have to, for efficiency
if (opp.Account != null && opp.OwnerId != opp.Account.OwnerId) {
opp.OwnerId = opp.Account.OwnerId;
opps.add(opp);
}
}
// At this point you can update the processed opportunities as needed, such as:
update opps;
Note that SOQL is smart: if there are multiple Opportunities that reference the same Account, each Opportunity's Account relationship points to the same in-memory Account record.
I just need data from directly related records but I'm in a trigger
In this scenario, relationships are "parent to child": the input records have Lookup or Master-Detail relationship fields to the related records.
In this case, you can't control the data in the input records. Triggers receive all fields for the input records, including relationship fields as IDs, but do not receive related object details in these records.
As such, you need to collect the related record IDs, then query them and use the query results for the subsequent processing.
The following is an example in the context of a before insert trigger for Opportunity, where again the Owner ID is to be set from the related Accounts.
Note the use of the Map<Id, SObject>(List<SObject>) constructor to turn a list of SObjects (Accounts here) into a Map indexed by the SObjects' IDs. This is an efficient way of providing fast access to the SObject using its Id, avoiding nested loops that increase computational complexity.
// Collect the unique account IDs, ignoring null for efficiency
Set<Id> accountIds = new Set<Id>();
for (Opportunity opp : Trigger.new) {
if (opp.AccountId != null) {
accountIds.put(opp.AccountId);
}
}
// Get the Accounts' Owner IDs and make the data efficient to use
Map<Id, Account> accsById = new Map<Id, Account>([SELECT Id, OwnerId FROM Account
WHERE Id IN :accountIds]);
// Now process the Opportunities to set their Owner ID to be the same as their Account's
for (Opportunity opp : Trigger.new) {
if (opp.AccountId != null) {
Account acc = accsById(opp.AccountId);
// We know that we must have an entry in the accsById for this Account ID, plus
// we don't need to worry about conditionally updating the value since the
// opportunity is already being updated (actually inserted; we are in a trigger, after all)
opp.OwnerId = acc.OwnerId;
}
}
I need data from indirectly related records based on one or more input record fields
In this scenario, there is no direct relationship between the input records and the "related" records, but some combination of values can be used to find the "related" records that "match" the input records.
Here's a case where one or more fields in the input records can be used to identify some other records (possibly without any direct relationship) from which some fields are to be retrieved and used to update the input records.
For example, you have a number of new Tasks being created and you want to set each task's WhatId to reference an open Case that has the same Priority as the Task and where the Case's ContactId is the same as the Task's WhoId.
To do this you must query the Cases with Status that is not "Closed", based on both Priority and ContactId, using values from all the Tasks then make it easy to find the Case for a given task.
In this situation, it's not possible to write a single SOQL query that finds exactly the Cases required. We "over-query", writing a query that we know will return the required Cases but also some Cases that match one, but not all, of the criteria, and use Apex to locate the desired Case for each Task.
Note that this example assumes the Case and Task Priorities share the same API names.
List<Task> tasks = …;
// Build the Priority and WhoId values from the input tasks
Set<String> priorities = new Set<String>();
Set<Id> whoIds = new Set<Id>();
for (Task task : tasks) {
// We allow for either or both of these being null and still match these with cases
priorities.add(task.Priority);
whoIds.add(task.WhoId);
}
// The following query may match more Cases than we need since there could be
// combinations of Priority and ContactId that we are not interested in. However,
// we know that it will cover all the combinations we do need when available
List<Case> cases = [SELECT Id, Priority, ContactId FROM CASE WHERE Status != 'Closed'
AND Priority IN :priorities AND ContactId IN :whoIds];
// Now arrange these into a structure for easy and efficient search against the Tasks
Map<Object[], Case> casesByKey = new Map<Object[], Case>();
for (Case case : cases) {
// Note that in this contrived example we may have more than one case that matches
// the Priority/ContactId combo. If so, this processing will keep the last Case matching
// the given combination. It may be that you always have unique combinations for
// your key values, you may want to use ORDER BY in the query to decide which to
// keep or you may apply some other strategy for choosing which to keep
casesByKey.put(new Object[]{case.Priority, case.ContactId}, case);
}
// Now we can get the Case for the Task and update the Task's WhatId to reference it
for (Task task : tasks) {
// Grab the equivalent, Case making sure to order the key constituent parts in
// the same order as when creating the map's keys
Case case = casesByKey.get(new Object[]{task.Priority, task.WhoId});
// Note that we don't guarantee to have a Case with the required combination
if (case != null) {
task.WhatId = case.Id;
}
}
// At this point the tasks are updated as needed, ready for whatever should happen next
The above example uses an Object array to construct a cheap "composite key". It's good for this purpose because it appropriately supports hashCode() and equals() (needed for any Map key type) based on its (ordered) content, and thus can be used safely as a key in a Map. Constructing an Object array is far more efficient compared to building a string key by concatenating the various values (as strings) together. It is, however, important that the keys are always built with the constituent parts in the same order each time.
Note that this Object array key approach works for composite keys of pretty much any length (not just 2, as shown here) and can contain values of any types that properly implement hashCode() and equals(), including instances of your own Apex classes.
