[SalesForce] Is it possible to combine these aggregate SOQL queries

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

Best Answer

I don't know offhand of a complete solution. You should be able to eliminate the first aggregate call by doing something like this:

Map<ID, ParentObject__c> ParentObjectMap = new Map<ID, ParentObject__c>(
[SELECT ID, ChildObjects_Created__c, ChildObjects_Unassigned__c, 
ChildObjects_Revenue__c, (Select ID from ChildObjects) FROM ParentObject__c 
WHERE ID IN :SetOfParentObjectIDs]);

(I'm assuming the relationship name is ChildObjects). You can then look at ParentObjectMap.get(someid).ChildObjects.Size() to find the number of child objects on a given parent.

In terms of the other two aggregate queries - here's a trick that might work for you:

Create two new numeric formula fields on the ChildObject. Set the first field to 1 if the Field__c field is set to 'Unassigned', zero otherwise. Set the second field to the value of the Net_Price_c field if the Status_c field is 'Paid', zero otherwise.

Now, you should be able to do a single aggregate query along the lines of:

AggregateResult[] groupedCountUnassigned = [SELECT COUNT(id), SUM(firstformulafield),
SUM(secondformulafield) FROM ChildObject__c WHERE ParentObjectID__c IN 
:SetOfParentObjectIDs GROUP BY ParentObjectID__c];

In effect, you're moving the filter condition from the SOQL query into the formula field (where it doesn't cost you against Apex limits). It is costing you a couple of fields, and depending on the amount of data you are dealing with could make a selective query non-selective, but for some applications, it can be a good solution.

Dan

Related Topic