Batch Apex - Need help

Question

This is my first time trying to use batch apex. I copied the example from Trailhead and attempted to move my class code into it. I don't think I have the aggregate query coded correctly but I don't have a clue how to do it. Can anyone review this and let me know?

global class UpdateStateCounts implements 
    Database.Batchable<sObject>, Database.Stateful {

   //Used for aggregrateresults
    Public Summary[] Summaries { get; set; }

    //Create Set with fields from Account page.
    Set<String> statesAcct = new Set<string>{
        'AL','AK','AZ','AR','CA','CO',
            'CT','DE','FL','GA','HI','ID',
            'IL','IN','IA','KS','KY','LA',
            'ME','MD','MA','MI','MN','MS',
            'MO','MT','NE','NV','NH','NJ',
            'NM','NY','NC','ND','OH','OK',
            'OR','PA','RI','SC','SD','TN',
            'TX','UT','VT','VA','WA','WV',
            'WI','WY',
            //Commonwealth/Territories
            'DC'};

    //Create List of active accounts
    list<account> myAccounts = new list<account>();
    myAccounts = [Select ID, Name from Account WHERE Type = 'Client'];

    //Create list to bulkify update command at end of loop
    list<account> accountsToUpdate = new list<account>();

   Summaries = new List<Summary>();

    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator(
            // AggregateResult[] groupedResults = 
            'SELECT COUNT(ID) ct, State__c st, Account__c acct ' + 
            'FROM Employees__c WHERE Type__c = \'Client\' AND Inactive__c != \'X\'' + 
            'GROUP BY Account__c,State__c'
        );
    }

    global void execute(Database.BatchableContext bc, List<Employees__c> scope){
        // process each batch of records

        for (AggregateResult ar : groupedResults) {
            Summaries.add(new Summary(ar));
        }

    //Loop MyAccounts
    for ( Account a : myAccounts ){

        //Create map of specific account aggregrate results per state. 
        Map<String, integer> aggregateAcct = new Map<String, integer>();


        //Loop - extract aggregrateResult data just for current account. 
        for(Summary s : Summaries ){
            //system.debug('Summary' + s.acctid);
            //system.debug('Account' + a.Id);

            if(s.acctId == a.id){
                aggregateAcct.put(s.stateName,s.stateCount);
                //System.debug('aggregateAcct - ' + s.stateName + '-' + s.stateCount); 
            } 
        }
        //End Loop - Summaries

        //Create State Name to State count field Map
        Map<String, integer> stateCount = new Map<String, integer>();

        //Link the state count to the field name that is used on the Account object 
        //This way we only get values for states that we have fields for on Account object.
        for(String st : statesAcct){
            StateCount.Put(st, aggregateAcct.get(st));  
        }

        //Now update each state field using our stored values. 
        //Values in StateCount map are now StateAbbreviation and Count

        a.AK__c = stateCount.get('AK');
        a.AL__c = stateCount.get('AL');
        a.AR__c = stateCount.get('AR');
        a.AZ__c = stateCount.get('AZ');
        a.CA__c = stateCount.get('CA');
        a.CO__c = stateCount.get('CO');
        a.CT__c = stateCount.get('CT');
        a.DE__c = stateCount.get('DE');
        a.FL__c = stateCount.get('FL');
        a.GA__c = stateCount.get('GA');
        a.HI__c = stateCount.get('HI');
        a.IA__c = stateCount.get('IA');
        a.ID__c = stateCount.get('ID');
        a.IL__c = stateCount.get('IL');
        a.IN__c = stateCount.get('IN');
        a.KS__c = stateCount.get('KS');
        a.KY__c = stateCount.get('KY');
        a.LA__c = stateCount.get('LA');
        a.MA__c = stateCount.get('MA');
        a.MD__c = stateCount.get('MD');
        a.ME__c = stateCount.get('ME');
        a.MI__c = stateCount.get('MI');
        a.MN__c = stateCount.get('MN');
        a.MO__c = stateCount.get('MO');
        a.MS__c = stateCount.get('MS');
        a.MT__c = stateCount.get('MT');
        a.NC__c = stateCount.get('NC');
        a.ND__c = stateCount.get('ND');
        a.NE__c = stateCount.get('NE');
        a.NH__c = stateCount.get('NH');
        a.NJ__c = stateCount.get('NJ');
        a.NM__c = stateCount.get('NM');
        a.NV__c = stateCount.get('NV');
        a.NY__c = stateCount.get('NY');
        a.OH__c = stateCount.get('OH');
        a.OK__c = stateCount.get('OK');
        a.OR__c = stateCount.get('OR');
        a.PA__c = stateCount.get('PA');
        a.RI__c = stateCount.get('RI');
        a.SC__c = stateCount.get('SC');
        a.SD__c = stateCount.get('SD');
        a.TN__c = stateCount.get('TN');
        a.TX__c = stateCount.get('TX');
        a.UT__c = stateCount.get('UT');
        a.VA__c = stateCount.get('VA');
        a.VT__c = stateCount.get('VT');
        a.WA__c = stateCount.get('WA');
        a.WI__c = stateCount.get('WI');
        a.WV__c = stateCount.get('WV');
        a.WY__c = stateCount.get('WY');
        a.DC__c = stateCount.get('DC');


        accountsToUpdate.add(a);

        }
        //End Loop - Account

        update accountsToUpdate;

    }       

    }    

    global void finish(Database.BatchableContext bc){

        // Do I need this part? Maybe send me an email when the process completes? 

    }    

}

Answer 1

I don't believe you want to run this type of aggregrate query in a batch class. Instead, you'll want to sort the data yourself in your method.

The query locator splits the records returned into separate batches for processing. With an aggregrate query it can't always split them up correctly for processing properly since it needs to run the aggregate counts on them first.

I believe this may be a case where you'll want to use the Stateful interface so you can maintain state between batches to keep track of some of these things. I'm not totally certain of this, but that's what my instincts are telling me.

Hopefully someone with more experience will jump in with a definitive answer for you.

Edit

I think @Cropredy was spot on with his suggestion that you could use a start() method that retrieves only Accounts and then in the execute() method perform an additional aggregate query for just the accounts within the current scope of execution within each batch of records.

The only downside to this that I can see is that if your job requires a large number of batches to process all your records, you'll be using up a lot of queries to process the entire scope. That's why it may be more efficient to do it in code, but this is certainly a very viable option for you to consider.