6

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? 

    }    

}
  • 2
    What exactly is your intended purpose? I can tell you now that your code is off, but I can't really tell what your intent is. – sfdcfox Apr 8 '16 at 21:00
  • 1
    We have all 50 states on the account page. The job of this code is to summarize the state field on the related Employee__c object and put those counts into the state fields on the account page. It is just doing a rollup essentially. I just can't create 51 rollups on the account page. – Matt M Apr 8 '16 at 21:40
  • FYI, my original version of this code works great in our sandbox with a few thousand records. My issue came to light in Production when I slammed into the 50000 record limit on the aggregrateresult query. – Matt M Apr 8 '16 at 21:43
  • What's the max data volume in production of Employee records per Account? – Mark Pond Apr 8 '16 at 22:08
  • 3
    your strategy should be to use a start() method that retrieves only Accounts and then in the execute() do the aggregate query for just the accounts in the execute's scope. – cropredy Apr 9 '16 at 0:42
4

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.

  • Queries are cheap. This is a great solution. – sfdcfox Apr 11 '16 at 4:30

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.