Bulk Apex iterator not working with aggregate query

Question

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

Answer 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;
}