The Aggregate Query Update Pattern

Introduction

We have all read the bulkification post on the developerforce board, but there are very few, if any, official documentation examples of how to actually go about this in practice. I have posted about the Aggregate Query Update pattern on several questions on the official forums, but it still seems to come up from time to time. So, I decided to write this quick post to describe what it is, and why a developer should use it. I named this pattern after the three steps one needs to bulkify any type of trigger or method. Most problems with governor limits can be resolved simply by following this pattern. We'll go over the steps in detail, and then look at an example.

Aggregate

In the first step, we aggregate together values that we want to process or query. The specific type of aggregate will depend on exactly what we are trying to do. Generally speaking, this will consist of creating a Set, and then using a loop to get the values we want. In some cases, we can skip this step, because we can use Trigger.new or Trigger.old to get the Id values of the records, particularly useful when you want to query child records. So, in general, the aggregate method will look like the following code.

Set<Object> values = new Set<Object>();

for(SObject record: records) {

    values.add(record.Field);

}

values.remove(null);

We should replace Object with the type of data we're using (typically String, Id, or Date, but we can use any type that's required), replace SObject with the type of record we're using, and Field with the name of the field. Here's a concrete example using the above pattern.

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

for(Contact record: Trigger.new) {

    emails.add(record.Email);

}

emails.remove(null);

We generally want to remove null values, because we cannot efficiently query null values, and we generally want to ignore those values anyways. In some cases, we can skip this step, such as when we're getting the Id or Name value of a record, because these fields are never null.

Query

In the second step, we typically need to get data from the database. This will generally either be the parents, children, or matching records we're interested in. This typically means that we will use the values we aggregated before. Generally, we need to have a Map to do this. In the general case, that means we will follow one of the following patterns.

Parent Records or Unique Values

Map<Object, SObject> records = new Map<Object, SObject>();

for(SObject record: [SELECT Field FROM SObject WHERE Field = :values]) {

    records.put(record.Field, record);

}

Records by Id

Map<Id, SObject> records = new Map<Id, SObject>(

    [SELECT Field FROM SObject WHERE Id = :values]

);

Child Records or Non-Unique Values

Map<Object, SObject[]> records = new Map<Object, SObject[]>();

for(Object value: values) {

    records.put(value, new SObject[0]);

}

for(SObject record: [SELECT Field FROM SObject WHERE Field = :values]) {

    records.get(record.Field).add(record);

}

Once we have the results from the query, we can then perform some action using the results.

Update

The third and final step is to perform an update. This involves using the Map to update values as appropriate. This usually follows one of two designs, as follows.

Copying Parent Data to Children from Child Records

for(SObject record: records) {

    if(record.ParentId != null) {

        record.Field = values.get(record.ParentId);

    }

}

Copying Parent Data to Children from Parent Records

for(SObject parent: records) {

    for(SObject child: children.get(record.Id)) {

        child.Field = parent.Field;

    }

}

Updating Parents with Children Data

for(SObject record: parents.values()) {

    record.Field = 0;

}

for(SObject record: records) {

    parents.get(record.ParentId).Field += record.Field;

}

Uses for the Aggregate Query Update Pattern

There's many things we can do with this pattern. We can detect and prevent or merge duplicate records, implement our own roll-up summary calculations, and copy values between children and parent records. In other words, we can perform almost all basic business functions using this pattern. It should be the most common tool in our arsenal. By using this one simple tool, we can reduce the number of DML statements and queries we use, and speed up bulk data loads.

Examples

Copy Account Address to Contact Address

trigger updateContactAddresses on Account (after update) {

    Map<Id, Contact[]> contacts = new Map<Id, Contact[]>();

    Contact[] updates = new Contact[0];

    for(Account record: Trigger.new) {

        contacts.put(record.Id, new Contact[0]);

    }

    for(Contact record: [SELECT AccountId FROM Contact WHERE AccountId = :Trigger.new]) {

        contacts.get(record.AccountId).add(record);

    }

    for(Account accountRecord: Trigger.new) {

        for(Contact contactRecord: contacts.get(record.Id)) {

            contactRecord.MailingStreet = accountRecord.BillingStreet;

            contactRecord.MailingCity = accountRecord.BillingCity;

            contactRecord.MailingState = accountRecord.BillingState;

            contactRecord.MailingPostalCode = accountRecord.BillingPostalCode;

            contactRecord.MailingCountry = accountRecord.BillingCountry;

        }

        updates.addAll(contacts.get(record.Id));

    }

    update updates;

}

Copy Account Phone when Account Changes

trigger copyPhoneOnContactCreate on Contact (before insert, before update) {

    Set<Id> accountIds = new Set<Id>();

    Contact[] changes = new Contact[0];

    for(Contact record: Trigger.new) {

        if(record.AccountId != null && (Trigger.isInsert ||

            Trigger.oldMap.get(record.Id).AccountId <> record.AccountId)) {

            changes.add(record);

            accountIds.add(record.AccountId);

        }

    }

    if(accountIds.isEmpty()) {

        return;

    }

    Map<Id, Account> accounts = new Map<Id, Account>(

        [SELECT Phone FROM Account WHERE Id = :accountIds]

    )

    for(Contact record: changes) {

        changes.Phone = accounts.get(record.AccountId).Phone;

    }

}

Sum All Contacts on an Account

trigger updateContactCount on Contact (after insert, after update, after delete, after undelete) {

    Set<Id> accountIds = new Set<Id>();

    if(Trigger.isInsert || Trigger.isUpdate || Trigger.isUndelete) {

        for(Contact record: Trigger.new) {

            accountIds.add(record.AccountId);

        }

    }

    if(Trigger.isUpdate || Trigger.isDelete) {

        for(Contact record: Trigger.old) {

            accountIds.add(record.AccountId);

        }

    }

    accountIds.remove(null);

    Map<Id, Account> updates = new Map<Id, Account>();

    for(Id accountId: accountIds) {

        updates.put(accountId, new Account(Id=accountId, ContactCount__c=0));

    }

    for(AggregateResult result: [SELECT AccountId Id, COUNT(Id) sum FROM Contact WHERE 

              AccountId = :accountIds GROUP BY AccountId]) {

        updates.get((Id)result.get('Id')).ContactCount__c = (Decimal)result.get('sum');

    }

    update updates.values();

}

Conclusion

I hope that this post will prove useful to future visitors. This is the most common pattern we use, and one of the most simple ones to learn. Using this pattern can save us governor limits and keep our code easy to read. Of course, I've left out some parts, such as error handling, minor optimizations, and so on, in order to avoid cluttering the code too much, but I hope that this code will help people get started using more efficient code.