function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
dmchengdmcheng 

Aggregate queries and Batch Apex: just not meant to work together?

So I'm trying to do an aggregate query in Batch Apex and of course I'm running into the error "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch" which is useless since of course the whole idea of an aggregation is get everything you need without a LIMIT.

 

I tried using the scope parameter on database.executeBatch because the documentation implies that it changes the batch size so I tried 3,000 since that is the quantity of records I have, but I still got the querymore error.

 

It looks like aggregate queries really cannot be used in Batch Apex.  Oh sure, for 20 records maybe, but not for 200+, i.e. the real world.  Can anyone confirm that?

 

Also, what does the scope parameter on database.executeBatch really do?  Does it only throttle down the batch size, or can you use it to throttle up the size?  If so, then why doesn't it work for aggregate queries?

 

Thanks

David

 

 

hisrinuhisrinu

Below is an excerpt from Apex PDF.

 

Note: Queries that include aggregate functions are subject to the same governor limits as other SOQL queries for
the total number of records returned. This limit includes any records included in the aggregation, not just the number
of rows returned by the query. If you encounter this limit, you should add a condition to the WHERE clause to reduce
the amount of records processed by the query.

 

 

Which clearly says that you can't aggregate on more than 10,000 records.

 

Scope parameter is used to change the batch size. By default the batch size of the batch apex is 200, but you can change it to <= 200

 

Hope this helps.

dmchengdmcheng

Yes but the Batch Apex section clearly says:

 

If you use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed.  A maximum of 50 million records can be returned

 

Plus, since I have 3,000 records, that should be well within the 10K limit anyway.

hisrinuhisrinu

It says that it can retrieve 50 million records in a sequential manner of batch size as 200 not in total.

 

Second question, I believe for 3K records it should work fine... I'm not sure in your case why is it giving an error.

 

I haven't done on batch apex, though I did it several times in normal class where it can support the aggregation

BritishBoyinDCBritishBoyinDC

As i understand it, the querylocator defines the universe of data that the batch needs to process, in the same way the dataloader can upload many thousands of records, but is still subject to the normal limits around triggers etc.

 

So you can use Aggregate queries - but I think you're right, the query can only return two hundred records at a time, and they are subject to normal limits. I don't think the scope can ever go above 200 since that is the limit for trigger execution etc...

 

But for instance, if I want to update all my Campaigns with the total sum of opportunities for those campaigns, I can do a query for 1000+ campaigns, and then pass that into the batch with the default scope

 

For each batch of 200, I can then do an aggregate query for all opportunities linked to those 200 campaign ids,  group by the campaign id, and then update those 200 Campaign records and onto the next batch. That does work...

dmchengdmcheng

@BritishBoy: I don't understand how you can do that within the Batch Apex syntax. Looking at the sample code here: Let's say the query string is "select Id from Campaign" and there are 1000 campaigns.

 

So the BatchableContext returns 1000 records into the scope object. It seems to me that the only way to process this is looping through the entire scope at once. How do you divide this scope into batches of 200 and how can you execute the aggregate queries without being the for loops?  Plus, what happens if you have more than 200 opps linked to a single campaign?

 

Also - I don't understand why aggregate queries cannot return more than 200 records unless there is a typo in the Governor Limits documentation - I see that SOSL is limited to 200 records, but SOQL outside of triggers is supposedly limited to 10K.

 

Thanks

David

 

 

global class CalcIt implements Database.Batchable<sObject> {

    Public String query; 

    //Execute the query.
    global database.querylocator start(Database.BatchableContext BC) {
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<sObject> scope) {
        for(sObject s : scope) {
			Campaign cmp = (Campaign)s;
			//code here.
		}
	}
	
    global void finish(Database.BatchableContext BC) {
    }

}

 

 

BritishBoyinDCBritishBoyinDC

So let's assume my QueryLocator returns a 1000 Campaigns. Each batch execute will query for and update 200 Campaigns - that's the scope of each batch.

 

I need to make sure that any Campaigns who used to be associated with opps but now have none are reset to zero, so I first of all loop through the batch scope, and place all 200  Campaign into a Map, with a value of zero . I can then execute an aggregate query for all optys linked to those 200 Campaigns.

 

With those results, I can then loop through the aggregate results, update the map, and then finally loop through and update a list.

 

Truth be told, I'm not sure what happens when the aggregate returns more than 200 records in  a batch context - I don't know if that would cause a querymore issue? Where I have used this code, there are usually many many more than two hundred optys linked to the 200 Campaigns being queries, but I know there can't be more than 10,000 associated opportunities in any one aggregate query.

 

So my execute looks like this: 

 

 

global void execute(Database.BatchableContext BC, List<sObject> scope){
Map<Id, Decimal> minteractions = new Map<Id, Decimal>();
//Set<Id> inids = new Set<Id>();
List<Campaign> interactions = new List<Campaign>();

for(sObject s : scope)
{Campaign c = (Campaign)s;
minteractions.put(c.Id,0);
}

AggregateResult[] gr = [SELECT CampaignId int, SUM(Amount)FROM Opportunity WHERE IsWon = TRUE AND CampaignId IN :minteractions.keyset() GROUP BY CampaignId];

for (AggregateResult ar : gr)  {
//System.debug('Sum' + ar.get('expr0'));
if (minteractions.containsKey((Id)ar.get('int'))) {
minteractions.put((Id)ar.get('int'), (Decimal)ar.get('expr0'));
}
}

For (Id intid: minteractions.keyset()) {
interactions.add(new Campaign(Id = intid, Event_Donations_to_Date__c = minteractions.get(intid)));
}

try
{
Database.update(interactions, false);
}
Catch (Exception Ex)
{system.debug(Ex);}
}

 

 

dmchengdmcheng

Thanks for posting, that was extremely helpful.  My mind was stuck on the wrong idea that I had to do all the processing within the for loop for the scope.

 

For anyone else who's trying to work with aggregate queries in batch apex:  here's what I was trying to do which was giving me the querymore error.  I need to calc YTD donations for contacts in the current fiscal year and update fields in the contacts.  The query string was:

 

select count(Opportunity.Amount)Qty, sum(Opportunity.Amount)Total from OpportunityContactRole where IsPrimary = true and Opportunity.CloseDate = THIS_FINANCIAL_YEAR group by ContactId

 

 

    global void execute(Database.BatchableContext BC, List<sObject> scope) {
		Contact[] changedCons = new List<Contact>();

		//Rather than retrieve existing contacts, we'll create new contact objects using the contact IDs
		//from the opportunity contact role and do an update with the resulting list.
		//Note we have to cast the data types for the aggregateresult fields. 		
        for(sObject s : scope) {
        	AggregateResult ar = (AggregateResult)s;
        	changedCons.add(new Contact(Id = (Id)ar.get('ContactId'), FYTD_Hard_Credit_Count__c = (Integer)ar.get('Qty'),
        	 FYTD_Hard_Credit_Amount__c = (Decimal)ar.get('Total')));
        }
        if(!changedCons.isEmpty()) update changedCons;
    }

 

 

DodiDodi

I am trying to do something similar but need to run about 11 more aggregated result queries prior to updating records.

 

For the method below, how could I apply varialbles that come from the additional aggregate queries? I need to modify the below line to bring in aggregate results from the other queries, but in intid is only within scope of the first aggregate query.

 

interactions.add(new Campaign(Id = intid, Event_Donations_to_Date__c = minteractions.get(intid)));

 

for (AggregateResult ar : gr)  {
//System.debug('Sum' + ar.get('expr0'));
if (minteractions.containsKey((Id)ar.get('int'))) {
minteractions.put((Id)ar.get('int'), (Decimal)ar.get('expr0'));
}
}

For (Id intid: minteractions.keyset()) {
interactions.add(new Campaign(Id = intid, Event_Donations_to_Date__c = minteractions.get(intid)));
}


 

 

 

BritishBoyinDCBritishBoyinDC

Couple of ways come to mind...

 

You could store all 11 results in a list variable in the map e.g.

Map<id, List<Decimal> > 

 

For the first query, create a new entry in the map, and put the result from the query in the first eklement in the list, and from then on, retrieve the list and update it so you have all 11 variables in a list, and then at the end, loop through the lsit and update the target object with the values that correspond to the position in the list

 

Or in the example, you could change the map to store the obejct e.g. 

Map<Id, Campaign> 

 

When you first add the Camapign to the map, you could set all target variables to 0, and then for each aggregrate query, update the particular value on the Campaign relevant to that value from the aggregrate query result 

DodiDodi

Thanks BritishBoy,

 

below is my existing code, it works for one query(jangr), but having issues getting results from the second(febgr). Any chance you can show me how to make it work for the second aggregated result, basically apply the febgr result to the Invoice_Avg_Feb__c field in the update method.

 

then I can apply the same logic to the other 10 query results. Thanks in advance for any help/advice. Thanks

 

global void execute(Database.BatchableContext BC, List<sObject> scope){
        Map<Id, Decimal> minteractions = new Map<Id, Decimal>();
        List<Account> interactions = new List<Account>();
        
        for(sObject s : scope)    {
            Account c = (Account)s;
            minteractions.put(c.Id,0);
        }
        
        AggregateResult[] jangr = [SELECT Account__c int, PostPeriod__c, AVG(InvoiceTotal2__c) avgjan From ESDInvoice__c e  WHERE Account__c IN :minteractions.keyset() AND CALENDAR_MONTH(PostPeriod__c) = 1 GROUP BY Account__c, PostPeriod__c];
        AggregateResult[] febgr = [SELECT Account__c int, PostPeriod__c, AVG(InvoiceTotal2__c) avgfeb From ESDInvoice__c e  WHERE Account__c IN :minteractions.keyset() AND CALENDAR_MONTH(PostPeriod__c) = 2 GROUP BY Account__c, PostPeriod__c];
        
        
        for (AggregateResult ar : jangr)  {
        //System.debug('Sum' + ar.get('expr0'));
            if (minteractions.containsKey((Id)ar.get('int'))) {
                minteractions.put((Id)ar.get('int'), (Decimal)ar.get('avgjan'));
            }
        }

        for (AggregateResult ar : febgr)  {
        //System.debug('Sum' + ar.get('expr0'));
            if (minteractions.containsKey((Id)ar.get('int'))) {
                minteractions.put((Id)ar.get('int'), (Decimal)ar.get('avgfeb'));
            }
        }

        
        
        For (Id intid: minteractions.keyset()) {
            interactions.add(new Account(Id = intid, Invoice_Avg_Jan__c = minteractions.get(intid), Invoice_Avg_Feb__c = minteractions.get(intid)));
        
        //System.debug('Status -- January Average for Account ' + intid + '= ' + minteractions.get(intid) + 'February Average for Account = ' ?? );
        
        }
        
                
        try
        {
            Database.update(interactions, false);
        }
        Catch (Exception Ex){
                system.debug(Ex);}
        }

BritishBoyinDCBritishBoyinDC

I can't compile this, but something like this should work - set the summary values to 0 for each account and add them to the map

 

Loop through aggregrations, and if they find a matching account in Map, update the account - it should just update the field on the object in the map, without actually needing to directly retreive the account into a local variable and then put it back into the map

 

You can then directly update the values in the map - no need to add to to a list first with this technique

 

One note - I suspect you can perform one single aggregration using group by cube, but I haven't actually tried that to confirm it, but check out the docs - would be much faster/more efficient if you could

 

global void execute(Database.BatchableContext BC, List<sObject> scope){
        Map<Id, Account> minteractions = new Map<Id, Account>();
        
        for(sObject s : scope)    {
            Account a = (Account)s;
		a.Invoice_Avg_Jan__c = 0;
        	a.Invoice_Avg_Feb__c = 0;

    		minteractions.put(a.Id,a);
        }
        
        AggregateResult[] jangr = [SELECT Account__c int, PostPeriod__c, AVG(InvoiceTotal2__c) avgjan From ESDInvoice__c e  WHERE Account__c IN :minteractions.keyset() AND CALENDAR_MONTH(PostPeriod__c) = 1 GROUP BY Account__c, PostPeriod__c];

        AggregateResult[] febgr = [SELECT Account__c int, PostPeriod__c, AVG(InvoiceTotal2__c) avgfeb From ESDInvoice__c e  WHERE Account__c IN :minteractions.keyset() AND CALENDAR_MONTH(PostPeriod__c) = 2 GROUP BY Account__c, PostPeriod__c];
        
        
        for (AggregateResult ar : jangr)  {
        //System.debug('Sum' + ar.get('expr0'));
            if (minteractions.containsKey((Id)ar.get('int'))) {
                minteractions.(Id)ar.get('int').Invoice_Avg_Jan__c = (Decimal)ar.get('avgjan');
            }
        }

        for (AggregateResult ar : febgr)  {
        //System.debug('Sum' + ar.get('expr0'));
            if (minteractions.containsKey((Id)ar.get('int'))) {
				minteractions.(Id)ar.get('int').Invoice_Avg_Feb__c = (Decimal)ar.get('avgfeb');
                
            }
        }

                
        try
        {
            Database.update(minteractions.values(), false);
        }
        Catch (Exception Ex){
                system.debug(Ex);}
        }

 

G!R!G!R!

Hi British boyin.........

 

When seeing this question , I have a doubt to raise....

 

as per my knowledge when the records are sent from 'start' method in batch apex to 'execute' method. lets say 3000 records..the execute method will convert this records in to batches of 200 each and process each batch at once.

 

now while invoking batch class ' if we give scope as 100, then the execute method willl separate the incoming records in to batches of 100 each.

 

here in doc says :

 

can you please explain the highlighted things below.

 

If thestartmethod returns a QueryLocator, the optional scope parameter ofDatabase.executeBatchcan have a maximum value of 2,000. If set to a higher value, Salesforce chunks the records returned by the QueryLocator into smaller batches of up to 2,000records.If thestartmethod returns an iterable, the scope parameter value has no upper limit; however, if you use a very high number, you may run into other limits.

BritishBoyinDCBritishBoyinDC

There are two ways of passing data into  a Batch - via a Query Locator, which uses standard SOQL to retrieve records from the database and process them. In this case, the docs are stating that the batch will, by default, split the records returned in to chunks of 200 records, but you can increase that number up to a maxium of 2000 records per execution - though that could cause issues with other governor limits.

 

You can also pass data into a batch using a custom iterator - in that case, you create the set of records to be processed via some custom programming, and the batch will not reduce the number to a maximum of 2000 records per execution, but all other governor limits still apply, so you would need to allow for that with any DML you execute. 

G!R!G!R!

Hi British Boyin,

 

Thanks for the responce and it very good explanation

 

I have another doubgt..if you could please correct me....

 

what is the difference between these two methods and what does the highlighted ones are

 

global void execute(Database.Batchablecontext BC, List<sObject> Scope); ---for querylocater in start method

 

global void execute(Database.Batchablecontext BC, List<P>); ---for Iterable<sobject> in start method

 


As I know 'BC' holds reference to the  'Batchablecontext ' object. But what are List<sObject> Scope and List<P> ---\\

 

in List<Sobject> Scope...is scope just a variable like any kind...or does it have anything to do with batch invoke method i.e

 

database.executebatch('jobname', variables, scope);

 

and most importantly..why do we use type casting in batch apex.....is it mandatory to use type casting here like for eg:

global void execute(Database.BatchableContext BC, List<sObject> scope)
   {

      for(Sobject s : scope)
      {
          //Type cast sObject in user object
          User ur = (User)s ;

 

 

Thanks in Advance....

textualtextual
I'm getting confused by this.

Im using ther iterator approach in my code because i need to group all attachments by parent id where parent type is account, which turns out is 185k attachments. bascially i want a count of all attachments on all accounts, so i thought grouping in parent id to get a count would be most efficient. and as mentioned above, i was hoping to group those 185k records o parent id, and the execute method would operate on them in chunks of 200. but with my limit of 2000 records and not being able to even add a field to the attachment to track processing, there no way to go through all the 185k attachments.
 
SELECT COUNT(Id) , ParentId, Parent.Type FROM Attachment
WHERE Parent.Type = 'Account'
GROUP BY ParentId, Parent.Type

How can I run this from Start so that Execute only processes 200 at a time?

Do I need to grab ALL attachments from the start and then process 200 at a time in Execute getting the count myself? That seems process intensive and inaccurate. Grouping has got to be the way

Right?
Yosef Cadaner 5Yosef Cadaner 5
Here are 2 possible solutions for accomplishing this:
  1. Implementing Iterable interface instead of Query Locator in batch class 
  2. Run the "Aggregate Query" in a schedule class by using @Read annotation. 
Full examples of implementation can be found here:
https://help.salesforce.com/apex/HTViewSolution?id=000192834&language=en_US
F SmoakF Smoak
Hi,

I am stuck in similar situation need help!!!
I am trying to retrieve all the calls created per account by any user on latest date in last 7days. This will be my query which will pass in database.querylocator. But I am getting similar error, your kind help will be appreciated:

global class BatchCreateAlertforCalls implements Database.Batchable<sObject> {
    global Database.QueryLocator start(Database.BatchableContext BC) {  
        
        String query = 'SELECT MAX(Submitted_DateTime__c),Account__c FROM Call__c where Status__c = \'Submitted_vod\' AND Submitted_DateTime__c >= LAST_N_DAYS:7 and Account__c !=null  group BY Account__c';
        return Database.getQueryLocator(query);
    }