[SalesForce] AggregateResult looping over empty query

I noticed weird behavior with for-loops when used with AggregateResult SOQL. Consider the following code (executed in the After Delete trigger context on the Order_Invoice_Item__c custom object):

trigger Test_Trigger on Order_Invoice_Item__c (before insert, before update, after insert, after update, after delete, after undelete)
{
    set<Id> prdIds = new set<Id>();

[90]:    System.debug('>>> prdIds: ' + prdIds);
for(AggregateResult ag : [SELECT Sum(Amount__c) amt, Sum(Quantity__c) qty, Product__c
                          FROM Order_Invoice_Item__c 
                          WHERE Product__c In : prdIds
                          Group By Product__c])
{

    Product2 prd = new Product2();
    prd.Id = (Id) ag.get('Product__c');
    prd.Average_Cost_Cal__c = (double) ag.get('amt')/(double) ag.get('qty');                                  
    prdList.add(prd);
[101]: System.debug('>>> ag: ' + ag);
}
[103]: System.debug('>>> prdList: ' + prdList);
}

The thing is prdIds is empty. But when the code is running this for-loop is executed one time, despite the fact that the SOQL query doesn't return any records.

Debug info:

Is it a side effect of for-loops when used with AggregateResult? Or am I missing something?

Best Answer

Your query clearly did return results. You have Order_Invoice_Item__c records whose Product__c is equal to null.

Related Solutions

[SalesForce] Before Insert: Using __r to pull related data in a Trigger.New for loop results in nulls even though lookup relationship has Id

You need to query for the parent records in order to get this information.

Set<Id> parentIds = new Set<Id>();
for (Child__c newRecord : trigger.new)
    parentIds.add(newRecord.Parent__c);
Map<Id, Parent__c> parents = new Map<Id, Parent__c>([
    SELECT Name FROM Parent__c WHERE Id IN :parentids
]);

Now you can loop through newList and figure out the corresponding parent record.

for(zqu__QuoteCharge__c quoteCharge: newList)
{
    zqu__ProductRatePlanCharge__c parent =
        parents.get(quoteCharge.zqu__ProductRatePlanCharge__c);
}

You actually have an issue with your logic that the above will not fix. If you insert sibling records with the same parent, their sum might be greater than the allowed maximum. You also need to factor in siblings already in the database. It should be something more like:

public static void validateMaximum(List<Child__c> newRecords)
{
    Map<Id, Decimal> parentToQuantity = new Map<Id, Decimal>();
    for (Child__c newRecord : newRecords)
    {
        if (!parentToQuantity.containsKey(newRecord.Parent__c))
            parentToQuantity.put(newRecord.Parent__c, 0);
        parentToQuantity.get(newRecord.Parent__c) += newRecord.Quantity__c;
    }
    // now you have the totals from the trigger context

    Map<Id, Parent__c> parents = new Map<Id, Parent__c>([
        SELECT Maximum__c, (SELECT Quantity__c FROM Children__r)
        WHERE Id IN :parentToQuantity.keySet()
    ];
    for (Parent__c parent : parents.values())
        for (Child__c child : parent.Children__r)
            parentToQuantity.get(parent.Id) += child.Quantity__c;
    // now you have a comprehensive total including existing values

    for (Child__c newRecord : newRecords)
    {
        Parent__c parent = parents.get(newRecord.Parent__c);
        if (parentToQuantity.get(parent.Id) > parent.Maximum__c)
            newRecord.addError('message');
    }
}

[SalesForce] Best way to investigate “System.LimitException: Too many SOQL queries: 101”

I find a good starting point when this happens is to capture the Debug Log with the developer console. Ensure you have the DB logging category is set to INFO or FINEST when you do so. Then run just one of the test methods that is failing due to the limit exception.

Every SOQL query that is made will be captured in the log. You can then start the investigation by looking for the SOQL queries that occur most frequently.

There are additional perspectives on each log that can be helpful, such as the Stack Trace Execution Tree and Execution Overview Limits tab (requires Finest Profiling logging level.)

Related Topic