2

(This is a first draft canonical Q&A, to be moved to the main SFSE later, as per the discussion on Query Bulkification Patterns looking for comments and input from other interested contributors.)

This is a canonical question and answer developed by the community to help address common questions. If you've been directed here, or your question has been closed as a duplicate, please look through the resources here and use them to shape more specific questions. To browse all canonical questions and answers, navigate to the canonical-qa tag.

This question and answer is owned by the community! If you can improve it and make it more useful, you're welcome to edit it.

I have a collection of records for which I need to query other directly or indirectly related records which I want to use to make further updates. When I perform the query per record in the input collection I keep hitting Salesforce query limits. What can I do to avoid this problem?

1 Answer 1

2

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.

I just need data from directly related records and I'm querying the input 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:

// We have some condition for loading the Opportunities. Here we assume we have some
// list of IDs
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 then 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 case you can't control the data in the input records. Triggers receive all fields for the input records, including lookups 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 these 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) constructor to turn a list of SObjects (Accounts here) into a Map indexed by the SObjects’ IDs – this is a really efficient way of providing fast access to the SObject using its Id:

// 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

Here's a case where one or more fields in the input records can be used to identify some other records 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. 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 because it appropriately supports hashCode and equals (needed for any Map key type) based on its 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, if required (and when written right).

8
  • I'd avoid using obscure syntax like new Opportunity[0].
    – Adrian Larson Mod
    Commented Oct 6, 2020 at 21:16
  • Fair enough. I will replace all but the object arrays with lists. Tomorrow :)
    – Phil W
    Commented Oct 6, 2020 at 21:39
  • I can't remember - don't collections containing null trigger a table scan when used in a WHERE Id IN : construction?
    – David Reed Mod
    Commented Oct 6, 2020 at 22:48
  • OK, arrays to lists and I've ensured not to collect null into the unique IDs set just in case. Anyone got any other patterns to add?
    – Phil W
    Commented Oct 7, 2020 at 7:10
  • Any other patterns on offer? Do you think this is worthy as a Community Q&A as it stands?
    – Phil W
    Commented Oct 7, 2020 at 17:11
  • @DavidReed I created this posting based on this. How do we make this "canonical"? I wondered if I should tick the "Community wiki" but wasn't sure. I probably should have...
    – Phil W
    Commented Oct 8, 2020 at 7:37
  • 1
    I've converted them to Community Wikis, @PhilW. Thank you!
    – David Reed Mod
    Commented Oct 8, 2020 at 17:41
  • I like your use of the object array to serve as a composite key; I've seen sfdcfox use an sobject of type Case for the same use case (because both fields are on Case). But Object[] is more general. Good tip!
    – cropredy
    Commented Oct 11, 2020 at 0:14

You must log in to answer this question.

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