0

I am at a bit of a loss at how to properly structure a batch apex query with an iterator in order to utilize batch apex with an aggregate query.

This is my current code

Iterator

global class AggregateResultIterator implements Iterator<AggregateResult> {
    AggregateResult[] results { get; set; }
    Integer index { get; set; }
    global AggregateResultIterator() {
        String query =
            'SELECT SUM(Total_Direct_Expense__c) directExpense, SUM(Gross_Revenue__c) grossRevenue, Opportunity__c ' +
            'FROM Commission__c ' +
            'GROUP BY Opportunity__c';
        results = Database.query(query);
        index = 0;
    }
    global boolean hasNext() {
        return results != null && !results.isEmpty() && index < results.size();
    }
    global AggregateResult next() {
        return results[index++];
    }
}

Iterable

global class AggregateResultIterable implements Iterable<AggregateResult> {
    global Iterator<AggregateResult> Iterator() {
        return new AggregateResultIterator();
    }
}

Batch Apex

global class TemporaryBulkOpportunityRun implements Database.Batchable<AggregateResult> {
    global Iterable<AggregateResult> start(Database.batchableContext info) {
        return new AggregateResultIterable();
    }
    global void execute(Database.BatchableContext BC, List<AggregateResult> scope) {
        List<Opportunity> rolledUpOpps = new List<Opportunity>();
        for (AggregateResult sinOpp : scope) {
            rolledUpOpps.add(
                new Opportunity(
                    Id = (Id) sinOpp.get('Opportunity__c'),
                    Total_Opportunity_Direct_Expense__c = (Decimal) sinOpp.get('directExpense'),
                    Gross_Opportunity_Revenue__c = (Decimal) sinOpp.get('grossRevenue')
                )
            );
        }
        update rolledUpOpps;
    }
    global void finish(Database.BatchableContext BC) {
}

When i call the batch class with

Id batchJobId = Database.executeBatch(new TemporaryBulkOpportunityRun());

and then review the debug logs, It runs through the iterator, looping the returned aggregate query (there are 1000 aggregateresults returned) but never seems to connect back to the execute method in the Batachable method to process the records. I have no clue what i am missing here. There arent any errors thrown, it just doesnt do anything

  • Also, for future reference: return results != null && !results.isEmpty() && index < results.size(); is unnecessary. Just: return index < results.size(); – sfdcfox May 1 at 3:57
1

Aggregate queries don't support queryMore, so instead of an iterator, just return the list directly:

global AggregateResult[] start(Database.batchableContext info) {
    return [
        SELECT 
            SUM(Total_Direct_Expense__c) directExpense, 
            SUM(Gross_Revenue__c) grossRevenue, 
            Opportunity__c
        FROM 
            Commission__c
        GROUP BY 
            Opportunity__c
    ];
}

If you're trying to aggregate every record in the database, this is not a scalable solution (you'll be limited to 2000 opportunities).

Instead, query for the opportunities, then do the aggregate result in the execute method:

global AggregateResult[] start(Database.batchableContext info) {
    return Database.getQueryLocator([
        SELECT Id FROM Opportunity
    ]);
}
global void execute(Database.BatchableContext BC, Opportunity[] scope) {
    Map<Id, AggregateResult> results = new Map<Id, AggregateResult>([
        SELECT 
            SUM(Total_Direct_Expense__c) directExpense, 
            SUM(Gross_Revenue__c) grossRevenue, 
            Opportunity__c Id
        FROM 
            Commission__c
        WHERE
            Opportunity__c = :scope
        GROUP BY 
            Opportunity__c
    ]);
    for(Opportunity record: scope) {
        AggregateResult result = results.get(record.Id);
        if(result != null) {
            record.Total_Opportunity_Direct_Expense__c = (Decimal)result.get('directExpense');
            record.Gross_Opportunity_Revenue__c = (Decimal)result.get('grossRevenue');
        } else {
            record.Total_Opportunity_Direct_Expense__c = 0.0;
            record.Gross_Opportunity_Revenue__c = 0.0;
        }
    }
    update scope;
}
| improve this answer | |
  • This explains why I couldn't reproduce the issue. I was intentionally limiting data volume and it never needed queryMore. – Avinash May 1 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.