Issue with Batch Apex Updating Results from AggregateResults SQOL Query

Question

I have an object Referral__C where we add a NHC_Tracker_ID(Nhc_Tracker__C another object). I want to update the fields on NHC tracker by count Referrals and Unique addresses related to Referral.

My Aggregate SOQL results query gives me right results when i do a trigger, but the business demands batch update as these values need to be update everyday.

When i save this query, i get this error: Class NhcTrackerBatchApex must implement the method: System.Iterable Database.Batchable.start(Database.BatchableContext)

Please let me know on how to resolve this issue.

global class NhcTrackerBatchApex implements Database.Batchable<AggregateResult> {

    global Database.Querylocator start(Database.BatchableContext bc) {
        String soqlQuery =   ' select Nhc_Tracker__c nhc, Count_distinct(Referral__C.Name) refCount' +
                             ' Count_distinct(address__c) leadcount,' +
                             ' from Referral__C' +
                             ' Where address__c != null' +
                                 ' AND Nhc_Tracker__c != null' +
                                 ' AND Nhc_Tracker__C = \'a4A2B00000009SBUAY\'' +
                                 ' AND (Referral_Type__c = \'New Construction\'' +
                                 ' OR  Referral_Type__c = \'Marketing Alliance\'' +
                                 ' OR  Referral_Type__c = \'Placeholder 3\')' +
                                 ' Group by Nhc_Tracker__c';

        return database.getquerylocator(soqlQuery);
    }

    global void execute(Database.BatchableContext bc, List<AggregateResult> nhcAggResults) {
        system.debug(':::::NhcfList' + nhcAggResults.size());
        if( nhcAggResults.size() > 0 ) {
            List<Nhc_Tracker__c> nhcListToUpdate = new List<Nhc_Tracker__c>();

            for( AggregateResult aggregateRow: nhcAggResults) {
                 Integer leadCount = (Integer)aggregaterow.get('leadcount');
                 Integer refCount = (Integer)aggregaterow.get('refCount');
                 Id trackerId = (Id) aggregaterow.get('nhc');
                 nhcListToUpdate.add(new NHC_Tracker__c(Id = trackerId ,Lead_Count__c = leadCount, Referral_Count__c = refCount));
            } 
            
             system.debug('::::: listToUpdate'  + nhcListToUpdate.size());
             if( nhcListToUpdate.size() > 0) {
                update nhcListToUpdate;
             }
        }
    }
        
    public void finish(Database.BatchableContext bc) { 
    }
}

Answer 1

Database.Batchable<AggregateResult> requires an Iterable<AggregateResult>. If you change your code to Database.Batchable<sObject>, your code would compile just fine. Note that AggregateResult queries are limited to 2000 rows, so if you have more than that, you'll get an exception.

As a side note, you can use an inline query instead of the "dynamic" string you have:

    return database.getquerylocator([
        select Nhc_Tracker__c nhc, 
               Count_distinct(Referral__C.Name) refCount, 
               Count_distinct(address__c) leadcount 
        from   Referral__c
        Where  address__c != null AND 
               Nhc_Tracker__C = 'a4A2B00000009SBUAY' AND 
               Referral_Type__c IN ('New Construction', 'Marketing Alliance', 'Placeholder 3')
        Group by Nhc_Tracker__c
    ]);