I'm using a few aggregate SOQL queries to mimic a rollup summary on an object that I don't necessarily want in a master-detail relationship. They are part of a trigger that fires when one of the child objects is inserted or updated. In the never-ending effort to reduce script statements and SOQL queries, I'm trying to figure out if there is a way to combine these 3 aggregate SOQL queries into one statement?
Map<ID, ParentObject__c> ParentObjectMap = new Map<ID, ParentObject__c>([SELECT ID, ChildObjects_Created__c, ChildObjects_Unassigned__c, ChildObjects_Revenue__c FROM ParentObject__c WHERE ID IN :SetOfParentObjectIDs]); AggregateResult groupedCountCreated = [SELECT COUNT(id) ct FROM ChildObject__c WHERE ParentObjectID__c IN :SetOfParentObjectIDs GROUP BY ParentObjectID__c]; AggregateResult groupedCountUnassigned = [SELECT COUNT(id) ct_unassigned FROM ChildObject__c WHERE ParentObjectID__c IN :SetOfParentObjectIDs AND Field__c = 'Unassigned' GROUP BY ParentObjectID__c]; AggregateResult groupedCountRevenue = [SELECT SUM(Net_Price__c) revenue FROM ChildObject__c WHERE ParentObjectID__c IN :SetOfParentObjectIDs AND Status__c = 'Paid' GROUP BY ParentObjectID__c];
As an example of what I'm trying to get to, if I was using SQL I might do something like this...
SELECT ParentObjectID__c, COUNT(*) AS ct, SUM(CASE WHEN Field__c = 'Unassigned' THEN 1 ELSE 0 END) AS ct_unassigned, SUM(CASE WHEN Status__c = 'Paid' THEN Net_Price__c ELSE 0 END) AS revenue FROM ChildObject__c WHERE ParentObjectID__c IN (<List of IDs>) GROUP BY ParentObjectID__c
I'd like to only have to loop through one result set in order to update the ParentObjectMap with the aggregate values instead of doing it for each query (3x in this case).
Any thoughts? Is there a better approach to this problem that I'm not considering? remove signature