Apex Code Development You need to sign in to do that
Don't have an account?
JWS AggregateResult -- Getting from AR object to SObject
I know I'm missing something really simple here, but I'm trying to optimize a trigger by implementing AggregateResult and am stumped about how to get from an AR object to an SObject. I'm trying to sum off children and update to parent.
Assume:
---------------------
Child object:
Child__c
Fields:
- childFieldA__c
- childFieldB__c
- childfieldC__c
Parent Object:
Parent__c
Fields:
- summedFieldA__c
- summedFieldB__c
- summedfieldC__c
So here's what I've got thus far:
trigger updateParent on Child__c (after insert, after update, after delete, after undelete) {
Set<Id> parentIdSet = new Set<Id>();
for(Child__c c : Trigger.new){
parentIdSet.add(c.Parent__c);
}
for (aggregateresult ar:[select Parent__c, sum(childFieldA__c) sumA, sum(childFieldB__c) sumB, sum(childFieldC__c) sumC From Child__c where Parent__c in :parentIdSet Group By Parent__c ]){
// (...big area of confused mystery exists here...)
}
}
For each Parent__c, I want to then go through and set the values like so: Parent__c.summedFieldA = ar.get('sumA')
... and then do a big update() at the end.
Keep in mind that I'm trying to optimize, here, and have been trying to keep loops out of loops and other practices. My main question is: now that I have the AR objects, how do I go through and update the Parent__c objects with the values I've summed in the AR object? I know this is something obviously simple, but it's just stumping me on how to make the two object set types interact -- AR and Parent__c.
Any help would be appreciated.
Jamie
Use a Map so you can directly associate the Id of the Group By with an object.
Your logic is in the wrong place.
Your Child__c trigger should just update the parents. The parents, in turn, should query their children. It would look like this:
trigger onChild on Child__c (after insert, after update, after delete, after undelete) { Map<Id,Parent__c> parents = new Map<Id,Parent__c>(); if(trigger.old!=null) { for(child__c c:trigger.old) { parents.put(c.parent__c,new parent__c(id=c.parent__c)); } } if(trigger.new!=null) { for(child__c c:trigger.new) { parents.put(c.parent__c,new parent__c(id=c.parent__c)); } } update parents.values(); }trigger onParent on Parent__c (before update) { for(AggregateResult ar:[SELECT Parent__c parentId,SUM(childFieldA__c) sumA,SUM(childFieldB__c) sumB, SUM(childFieldC__c) sumC FROM Child__c WHERE Parent__c IN :Trigger.new GROUP BY Parent__c]) { Parent__c p = Trigger.newMap.get((Id)ar.get('parentId')); p.summedFieldA__c = (Decimal)ar.get('sumA'); p.summedFieldB__c = (Decimal)ar.get('sumB'); p.summedFieldB__c = (Decimal)ar.get('sumC'); } }Does this help?
Update: I tried the solution above and it works really well -- thanks. If I wanted to go to a "grandparent" level, how could I tweak the parent's trigger to ensure that a) its own fields were updated and b) that it also triggered the grandparent to look downward?
trigger onChild on Child__c (after insert, after update, after delete, after undelete) { Map<Id,Parent__c> parents = new Map<Id,Parent__c>(); if(trigger.old!=null) { for(child__c c:trigger.old) { parents.put(c.parent__c,new parent__c(id=c.parent__c)); } } if(trigger.new!=null) { for(child__c c:trigger.new) { parents.put(c.parent__c,new parent__c(id=c.parent__c)); } } update parents.values(); } trigger onParent on Parent__c (after insert, before update, after update, after delete, after undelete) { if(trigger.isbefore) { for(AggregateResult ar:[SELECT Parent__c parentId,SUM(childFieldA__c) sumA,SUM(childFieldB__c) sumB, SUM(childFieldC__c) sumC FROM Child__c WHERE Parent__c IN :Trigger.new GROUP BY Parent__c]) { Parent__c p = Trigger.newMap.get((Id)ar.get('parentId')); p.summedFieldA__c = (Decimal)ar.get('sumA'); p.summedFieldB__c = (Decimal)ar.get('sumB'); p.summedFieldB__c = (Decimal)ar.get('sumC'); } } else { Map<Id,GrandParent__c> grandparents = new Map<Id,GrandParent__c>(); if(trigger.old!=null) { for(parent__c p:trigger.old) { grandparents.put(p.grandparent__c,new grandparent__c(id=p.grandparent__c)); } } if(trigger.new!=null) { for(parent__c p:trigger.new) { grandparents.put(p.grandparent__c,new grandparent__c(id=p.grandparent__c)); } } update grandparents.values(); } trigger onGrandParent on GrandParent__c (before update) { for(AggregateResult ar:[SELECT GrandParent__c parentId,SUM(summedFieldA__c) sumA,SUM(summedFieldB__c) sumB, SUM(summedFieldC__c) sumC FROM Parent__c WHERE GrandParent__c IN :Trigger.new GROUP BY GrandParent__c]) { GrandParent__c gp = Trigger.newMap.get((Id)ar.get('parentId')); gp.summedFieldA__c = (Decimal)ar.get('sumA'); gp.summedFieldB__c = (Decimal)ar.get('sumB'); gp.summedFieldB__c = (Decimal)ar.get('sumC'); } }You see what I did there? You can continue the pattern up to 16 levels deep, after which you'll need to move some of the updates to a @future method to avoid trigger recursion limits.
Thanks, sfdcfox. That's helpful. I implemented something like it and am getting the "found-frequently-in-Force-board-search" Apex error of "System.FinalException: Record is read-only".
It appears that when I go to the grandparent (which has a 1-to-1 relationship with its children) and try to do a before update field update, I get the read-only error.
If I comment out the field update on the grandparent, the other two levels of trigger work fine.
Do you see anything that would cause this issue with your approach, above?
Make sure your code isn't being called during "after update" for some reason. You might need to wade through some debug logs. My example code should be working perfectly assuming that exact pattern is followed. I've actually had to have a recursive trigger that went about 4-5 levels deep, and it worked just fine. Check your IF statements. If you still need help, send me a PM and I'll take a look at it.
Thanks. PM sent.