Monday, 23 October 2017

Aggregate queries in Batch Apex



Recently, I came across one requirement, in which i need to use aggregate queries in batch apex .My requirement was to get maximum annual revenue of different account types.


I received below error message.


"Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch". I spent good time to find its solution.


To fix this error what we should do.


1. Create an Apex class implements Iterator<AggregateResult>.
2. Create an Apex class implements Iterable<AggregateResult>.
3. Implementing to Database.Batchable<AggregateResult>, and Using Iterable at start execution in Batch Apex.


Iterable Class

global class AggregateResultIterable implements Iterable<AggregateResult> {
private String query;

global AggregateResultIterable(String soql){
query = soql;
}


global Iterator<AggregateResult> Iterator(){
return new AggregateResultIterator(query);
}
}


Iterator Class

global class AggregateResultIterator implements Iterator<AggregateResult> {

AggregateResult [] results {get;set;}

// tracks which result item is returned

Integer index {get; set;}



global AggregateResultIterator(String query) {
index = 0;
results = Database.query(query);
}


global boolean hasNext(){
return results != null && !results.isEmpty() && index < results.size();
}


global AggregateResult next(){
return results[index++];
}
}

Batch Class

global class RunQuery implements Database.Batchable<AggregateResult> {

// The batch job starts

global Iterable<AggregateResult> start(Database.BatchableContext bc){

String query = 'select name, type, max(AnnualRevenue) revenue from account where annualRevenue!= null group by name, type order by max(AnnualRevenue) desc';

return new AggregateResultIterable(query);
}

// The batch job executes and operates on one batch of records

global void execute(Database.BatchableContext bc, List<sObject> scope){
list <Account_Revenue__c> AR=new list<Account_Revenue__c>();

for(sObject sObj : scope) {
Account_Revenue__c c=new Account_Revenue__c();
AggregateResult a = (AggregateResult)sObj;

c.Name__c=string.valueof(a.get('name'));
c.Annual_Revenue__c=integer.valueof(a.get('revenue'));
c.Type__c=string.valueof(a.get('Type'));
AR.add(c);
}

database.insert(ar,false);
}

// The batch job finishes
global void finish(Database.BatchableContext bc){ }
}


We can schedule job using steps mentioned in this post.








at