function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Patrick ConnerPatrick Conner 

Workaround for Aggregate query does not support queryMore()

Below is an outline of a schedulable class I'm trying to deploy. 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. 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. 

Any help would be greatly appreciated. Thank you.
Raj VakatiRaj Vakati
Hi Patrick  ,

Here is my tough, its may work in your case. 
  1. You can use batch apex with Database.Stateful 
  2. Write your own Aggregate logic to perform the bulk data handling 
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_batch_interface.htm



 
Patrick ConnerPatrick Conner
Thank you for your time. database.stateful may make sense, as it seems to allow me to run the entire query in batches without losing holistic data (to find, for example, newest task across all batches). Is that correct? I'm still having trouble figuring out how to resolve these issues. Any additional help would be appreciated, thanks again!
Raj VakatiRaj Vakati
Yes . You run the query without losing the history with the stateful interface. Sample code is here  
global class SummarizeAccountTotal implements 
    Database.Batchable<sObject>, Database.Stateful{

   global final String Query;
   global integer Summary;

   global SummarizeAccountTotal(){
Query='Select whatId from task ';
     Summary = 0;
   }

   global Database.QueryLocator start(Database.BatchableContext BC){
      return Database.getQueryLocator(query);
   }

   global void execute(
                Database.BatchableContext BC, 
                List<sObject> scope){
      for(sObject s : scope){
         // Logic here to show the sum 
      }
   }

global void finish(Database.BatchableContext BC){
   }
}