Introduction
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.
