Test Class for Scheduled Batch Class - Error

Question

I have a scheduled batch class that will eventually run on the 1st of every month. It's designed to find records from an Asset that are linked to Accounts, aggregate the counts of these records into 3 categories (New, Old, Active), and then create a single custom object (Track__c) record for each associated account.

I created the test class below as a start (it still needs Assertions) but I'm getting an error message:

System.ListException: Row with null Id at index: 0 || Class.MembChurnRecordsBatch.execute: line 16, column 1

I'm don't know how to address this, can anyone advise?

Test Class:

@istest
public class RecordsTest{
    
    public static testmethod void testBatch1() {
        Test.startTest();
        
        Account account = TestUtility.createAccount('Prospect', RECORDTYPEID_ACCOUNT_TYPE);
        insert account;
        
        Contact contact = TestUtility.createContact(account.Id, RECORDTYPEID_CONTACT_TYPE, 'Primary');
        insert contact;
        
        Asset rec = new Asset(AccountId=account.Id,Name='Test',StartDate=System.today().addMonths(-3),EndDate=System.Today());
        insert rec;
        
        BatchSchedule__c batchSchedRec = new BatchSchedule__c();
        insert batchSchedRec;

         string chron = '0 0 23 * * ?';
            string jobid = System.schedule('testScheduledApex', chron, new RecordsSchedule());
            CronTrigger ct = [Select id , CronExpression from CronTrigger where id = :jobId];

        Tracker__c track = new Tracker__c();
            track.Account_Name__c=account.Id;
            track.Active__c=0;
            track.New__c=0;
            track.Old__c=0;
            track.Date__c=System.today();
        insert track;

        batchSchedRec = [SELECT Id,Scheduled_Id__c FROM BatchSchedule__c WHERE ID =: batchSchedRec.Id];
            batchSchedRec.Scheduled_Id__c = ct.Id;
        update batchSchedRec;
        
        RecordsBatch testBatch = new RecordsBatch();

        Database.executebatch(testBatch);      
        Test.stopTest();
    }
    
    
    public static testmethod void testSchedule() {
        Test.startTest();
        
        BatchSchedule__c batchSchedRec = new BatchSchedule__c();
            batchSchedRec.Scheduled_Id__c = '0';
        insert batchSchedRec;
        
        RecordsSchedule testSched = new RecordsSchedule();
        String sch = '0 0 1 1 * ? *';
        system.schedule('Test status Check',sch,testSched);
        
        Test.stopTest();
        
    }
}

Class:

public class RecordsBatch implements Database.Batchable<sObject> {
    public Database.QueryLocator start(Database.BatchableContext context) {
        return Database.getQueryLocator([SELECT Id FROM Account WHERE Id IN (SELECT AccountId FROM Asset)]);
    }

Date PriorMonth = System.today().toStartOfMonth().addMonths(-1);
Date CurrentMonth  = System.today().toStartOfMonth();
Integer OldAmt;
Integer NewAmt;
Integer ActiveAmt;

    public void execute(Database.batchableContext context, List<Account> scope) {

        List<Tracker__c> RecList = new List<Tracker__c>();

        Map<Id, AggregateResult> activeRec = new Map<Id, AggregateResult>([
            SELECT AccountId Id1, COUNT(Id) amt1, EndDate
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != Null AND EndDate >=:CurrentMonth AND EndDate <> NULL GROUP BY AccountId, EndDate]);

        Map<Id, AggregateResult> newRec = new Map<Id, AggregateResult>([
            SELECT AccountId Id2, COUNT(Id) amt2, StartDate, EndDate
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != Null AND StartDate >= :PriorMonth AND StartDate <> NULL AND StartDate < :CurrentMonth GROUP BY AccountId, StartDate, EndDate]);            

        Map<Id, AggregateResult> oldRec = new Map<Id, AggregateResult>([
            SELECT AccountId Id3, COUNT(Id) amt3, EndDate
            FROM Asset 
            WHERE AccountId = :scope AND AccountId != Null AND EndDate >= :PriorMonth AND EndDate <> NULL AND EndDate < :CurrentMonth GROUP BY AccountId, EndDate]);

        for(Account record: scope) {
            AggregateResult activeCount = activeRec.get(record.Id);
                if(activeCount != null) {
                    ActiveAmt = (Integer)activeCount.get('amt1');
                } else {
                    ActiveAmt = null;
                }
            AggregateResult newCount = newRec.get(record.Id);
                if(newCount != null) {
                    NewAmt = (Integer)newCount.get('amt2');
                } else {
                    NewAmt = null;
                }
            AggregateResult oldCount = oldRec.get(record.Id);
                if(oldCount != null) {
                    OldAmt = (Integer)oldCount.get('amt3');
                } else {
                    OldAmt = null;
                }
            Tracker__c ct = new Tracker__c(Account_Name__c = record.Id, Date__c = PriorMonth, Active__c = ActiveAmt, New__c = NewAmt, Old__c = OldAmt);
                RecList.add(ct);
            }
        insert RecList;
    }

    public void finish(Database.BatchableContext bc)
    {
        BatchSchedule__c b = BatchSchedule__c.getOrgDefaults();
            b.Scheduled_Id__c = system.scheduleBatch(new RecordsBatch(),'Batch 2'+System.currentTimeMillis(),2);
        upsert b;
    }
}

Answer 1

You have made a mistake by changing the field alias. That is actually a key contract that makes the Map constructor work with AggregateResult. If you think about the fact the Map<Id, SObject> constructor essentially calls get('Id') to get the map key, it's fairly intuitive.

Correct

SELECT Parent__c Id FROM MyObject__c GROUP BY Parent__c

Incorrect

SELECT Parent__c Id1 FROM MyObject__c GROUP BY Parent__c

---

Nota Bene - Your tests will be faster and more robust if you use Dependency Injection. I proposed a theoretical approach here and can attest that it works well as a simple injection mechanism. It does break the Map<Id, AggregateResult> magic as provided, but overloading the method signatures to convert this structure ought to be a straightforward exercise for the reader.

Dependency Injection on standard queries is highly recommended as well, to minimize database interaction in your test suite. Forcing individual unit tests to hit the database incurs all CPU time (and clock time) expenses from your domain logic. It also risks hitting validation errors. Hence why I say above injection makes your tests faster and more robust, respectively.