Easy Rollups using aggregate queries
So one thing I know that almost every salesforce user has wanted to do at some point is do rollups between objects that don’t have master detail relationships. I know this topic has been covered before by building custom counter code, but usually it’s kind of bulky and may have governor limit problems. What I have here is a simple an effective way to do your rollups using the under appreciated aggregate query function.
Essentially the idea is, your function takes a list of records. Iterate through the list, and find the Id’s of any related parent object. So if you were trying to calculate a rollup field on accounts that sum some value on contacts, your trigger would be on the contacts, and add every account id encountered in any of those contacts to a set.
Then using that set, run an aggregate query that does your summing or whatever for all those accounts. Iterate over the returned data set, extract the values, add them to the account object, then update the accounts. It might look something like this (provided you have a field on your contact object called age__c that is numeric and a numeric field on your account called totalAge__c).
The first two lines about creating contacts is just so you can test this in execute anonymous mode. I wanted to create some sample data. Like I said, normally the data you’d be iterating over to build the list of accountIds would be the contacts in the trigger context. When you go to use this, you can of course remove those lines.
//just get some sample contacts to play with. Normally the contacts would be in the trigger.new scope
list<contact> contacts = new list<contact>();
contacts = [select accountid from contact limit 500];
set<Id> accountIds = new set<Id>();
list<account> updateAccounts = new list<account>();
//add all accounts that are going to need their counters updated to the set
for(contact c : contacts)
{
accountIds.add(c.accountid);
}
//run the query to sum the data
AggregateResult[] totalAges= [select SUM(age__c)totalAge,accountId
From Contact
group by ROLLUP (accountId)];
//loop over the result set. One row per account with a totalAge and accountId column
for(AggregateResult ar: totalAges)
{
Id thisAccountId = string.valueOf(ar.get('accountId'));
Account thisAccount = new Account(Id=thisAccountId);
thisAccount.totalAge__c = double.valueOf(ar.get('totalAge'));
updateAccounts.add(thisAccount);
}
update updateAccounts;
This works really well as an after trigger. Call it as an after trigger for insert, update, delete, and undelete. For insert update, and undelete pass it the trigger.new context of contacts, and for delete pass it the trigger.old context.
Anyway, I’ve used this approach several times and it works very nicely. It’s bulk safe, fairly fast, and easy to maintain and add many more counters to. You can just keep creating aggregations and fields to assign them to on the parent object. Hope this helps someone.
I Write Crappy Code 