+ Start a Discussion

Help with SOQL

Hi all ,

I need help with SOQL query . I have two objects namely "Account" and "Relationship"
Account object has custom field call "Current Value"
Relationship  object has 2 lookup fields( acc1 and acc2)  to Account. It also has custom field called "Total Value"

I'm trying to write a query that will return me SUM of all the "Current Value" for list of AccountID's  grouped by Relationship 

Thanks for help.
Hi ,

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.

Hope this will help