Limit Workaround for Aggregate Query

Question

Below is an outline of a schedulable class I'm trying to deploy. The goal of the class is to keep all tasks created in last 30 days, and, if there are no tasks in the last 30 days to keep only the most recent one.

I'm getting the following error on schedule (and in developer console), due to too many IDs in queries:

Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch.

global class TaskRetention implements Schedulable
{
    global void execute(SchedulableContext SC)
    {
        AggregateResult[] tasksWithin30Days = [Select whatId from task where createddate = Last_n_days:30 group by whatid];
        AggregateResult[] noTask30Days = [Select whatId,max(createddate) maxdate from task group by  whatid having max(createddate) < Last_n_days:30 ];

        Id[] hasTaskWithin30Days = new Id[]{};
        Id[] noTasksIn30Days = new Id[]{};
        String[] whatIdDateTimeList = new String[]{};

        for(AggregateResult ar: tasksWithin30Days ){
            hasTaskWithin30Days.add((Id)ar.get('whatId'));
        }

        for(AggregateResult ar: noTask30Days){
            noTasksIn30Days.add((Id)ar.get('whatId'));
            whatIdDateTimeList.add(''+ar.get('whatId')+ar.get('maxdate'));
        }

        delete [ Select Id from Task where whatId in :hasTaskWithin30Days and createddate< last_n_days:30];
        delete [Select Id from Task where whatId in :noTasksin30Days and whatId_createddate__c not in :whatIdDateTimeList];
    }

}

I can limit the results to 2k, but this renders the class ineffective (Perhaps not with Database.Stateful?). I've read of using QueryMore as a workaround, referencing last returned whatID perhaps? I've also seen references to iterable interfaces and @read, but I don't know how to employ either of those.

Answer 1

Not sure you need the AggregateQuery, a regular query should work and you may be able to combine into a single query if your data size is not prohibitive:

Task[] tasksWithin30Days = [Select whatId, createdDate from task where 
                            createddate = Last_n_days:30 order by createdDate];

then

Task[] tasksToDelete = new Task[]{}; //List of tasks to delete
Set<Id> whatIdTaskSaved = new Set<Id>(); //Keep track of whatIds that had a task saved
Date cutoff = date.today().addDays(-30);

for(Task t : tasksWithin30Days){
   if(t.createdDate < cutoff){ //If we are outside the 30 days - may not be needed in this iteration example if the query filters already
      if(whatIdSaved.contains(t.whatId){ //If we have already saved one task
           tasksToDelete.add(t);
      }
   }

   if(whatIdSaved.contains(t.whatId) == false){
        whatIdSaved.add(t.whatId); //save the one task - since we are ordered it saves the newest one
   }
}
.....//Do the other query values
delete tasksToDelete;

Now I am not sure you can query for every task in the scheduler but if you can then you only need the one query.

The basic concept of the code is:

1. Ordered results
2. For each WhatId, save the first task
3. For all the remaining tasks for a given what Id if < cutoff date add to delete list

Adjust the logic accordingly based on the values you are iterating over. End result will be a list of Tasks to delete