0

I have a trigger logic that runs when an Opportunity stage changes and updates/inserts/deletes a bunch of related records. For some Opportunities where there are 300+ OpportunityLineItems I get CPU timeouts. So I put in a batch job to count the number of OLIs and if it's over the limit I preset it will do it in batch instead of one transaction.

My problem is that the batch doesn't seem to kick off when run by the trigger, if I run it in anon apex it runs no problem but doesn't seem to want to fire. I put debugs and watch the jobs queue and nothing.

From the trigger helper I'll pass it over like this:

Batch_RevenuePipelineHandler.createRevPipeline(oppySet);    

This is the batch class that I am working with.

global class Batch_RevenuePipelineHandler implements Database.Batchable<sObject>
    {
       Set<Id> oppySet;  

    /*
        * MethodName        : Batch_RevenuePipelineHandler
        * param             : Set<Id> oppyIds
        * Description       : This method will kick off the batch process
    */

    public Batch_RevenuePipelineHandler(Set<Id> oppySet)
    {
        this.oppySet = oppySet;
    }

    /*
        * MethodName        : start
        * param             : 
        * Description       : This method will get the query to use in the batch
    */ 

    global Database.QueryLocator start(Database.BatchableContext BC) 
    {
        String QUERY = getStringQuery(oppySet);
        return Database.getQueryLocator(QUERY);
    }

    /*
        * MethodName        : execute
        * param             : List<sObject> scope
        * Description       : This method will execute the methods in the batch
    */ 

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

    /*
        * MethodName        : finish
        * param             : 
        * Description       : This method will get the query to use in the batch
    */ 

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

    /*
        * MethodName        : getStringQuery
        * param             : 
        * Description       : This method will get the string that makes up the query
    */   

    public static String getStringQuery(Set<Id> oppySet)
    {
        String query =
        'SELECT ' +
                'Id, ' +
                'Product2Id ' +
        'FROM   OpportunityLineItem ' +
        'WHERE  OpportunityId IN :oppySet ';
        return query;
    }  

    public static void createRevPipeline(Set<Id> oppySet)
    {
        // Number of opportunity items that can be handled within a single execution context taken from custom setting
        Revenue_Pipeline_Settings__c setting = Revenue_Pipeline_Settings__c.getInstance();
        Integer MAX_OLI_IN_BATCH = Integer.valueOf(setting.Batch_Size__c);  
        Integer num = Database.countQuery('SELECT count() FROM OpportunityLineItem WHERE OpportunityId IN :oppySet');
        
        // we need to kick off a batch then
        if(num > MAX_OLI_IN_BATCH)
        {
            Database.executeBatch(new Batch_RevenuePipelineHandler(oppySet), MAX_OLI_IN_BATCH);  
        } 
        else
        {  
            String QUERY = getStringQuery(oppySet);
            RevenuePipelineHandlerOLI.createRevPipe((List<OpportunityLineItem>)Database.query(QUERY));
        }   
    }  
}

What am I doing wrong that it won't fire?

Edit:

I use trigger handlers so I filter after update in my class:

public static void filterAfterUpdate(Map<Id, Opportunity> oldMap, Map<Id, Opportunity> newMap)
{
    Set<Id> oppySet = new Set<Id>();
    Revenue_Pipeline_Settings__c setting = Revenue_Pipeline_Settings__c.getInstance();
    Boolean iskilled = setting.KillSwitch__c;
    
    // Check if the revenue pipeline should be created
    for(Opportunity oppy : newMap.values())
    {
        if(
            iskilled == false
        )
        {
            oppySet.add(oppy.Id);
        }
    }
    if(oppySet.size() > 0)
    {
        createRevenuePipeline(oppySet);
    }
} 


public static void createRevenuePipeline(Set<Id> oppySet)
{
    NPD_Batch_RevenuePipelineHandler.createRevPipeline(oppySet);
}
8
  • 1
    Its my guess because you do not provide number of records which you have in a setting, but probably you'll batch will never run because trigger process up to max 200 records at once in one run. Therefore it will never reach 300 records at once if that is in your setting Commented Feb 18, 2022 at 19:59
  • Use edit and include the trigger
    – cropredy
    Commented Feb 18, 2022 at 20:05
  • I do have a value in the setting, it does hit 300 because I would have say 1 Opportunity Id in the set and 300 Opportunity products in the query. That's why I need to break it up somehow with the batch. Commented Feb 18, 2022 at 20:12
  • @DanWooding, I assume this is a hierarchy custom setting - have you defined it with org wide scope? Commented Feb 19, 2022 at 8:44
  • 1
    One of the most annoyingly pervasive misconceptions about Apex out there.
    – Adrian Larson
    Commented Feb 20, 2022 at 0:45

1 Answer 1

3

Your problem seems to be the way you are using a dynamic query. See, the bind variable only works within the same context that the query method is called.

Take, for example, this simple class that iterates through the account records in a list of Ids:

public class AccountProcessingBatch implements Database.Batchable<SObject> {
    public String getStringQuery(Set<Id> recordIds) {
        String query = 'SELECT Id FROM Account WHERE Id IN :recordIds';
        return query;
    }

    public Database.QueryLocator start(Database.BatchableContext ctx) {
        String queryString = getStringQuery(getAccountIds());
        System.debug(queryString);
        return Database.getQueryLocator(queryString);
    }

    private Set<Id> getAccountIds() {
        Set<Id> result = new Set<Id>();

        for (Account record : [SELECT Id FROM Account LIMIT 10]) {
            result.add(record.Id);
        }

        return result;
    }

    public void execute(Database.BatchableContext ctx, List<Account> scope) {
        System.debug('Hello world!');
    }

    public void finish(Database.BatchableContext ctx) {
        System.debug('Good bye!');
    }
}

Nothing wrong with it on first look, right? But running it with the following command succeeds but fails at the start method:

Database.executeBatch(new AccountProcessingBatch());

That happens because at runtime your record ids are not passed onto the query itself because they are fetched in a different function context (the getStringQuery context) than the one where the database call is executed.

Notice the following:

public String getStringQuery(Set<Id> recordIds) {
    String query = 'SELECT Id FROM Account WHERE Id IN :recordIds';
    return query;
}

public Database.QueryLocator start(Database.BatchableContext ctx) {
    String queryString = getStringQuery(getAccountIds());
    System.debug(queryString);
    return Database.getQueryLocator(queryString);
}

This fails with an exception:

17:04:58:044 EXCEPTION_THROWN [10]|System.QueryException: Variable does not exist: recordIds

Because the variable recordIds only exists within the context of the getStringQuery method. It does not exist on the start method, where the database is called.

To fix this, a simple change is made:

public String getStringQuery() {
    String query = 'SELECT Id FROM Account WHERE Id IN :recordIds';
    return query;
}

public Database.QueryLocator start(Database.BatchableContext ctx) {
    String query = getStringQuery();
    // notice how "recordIds" exists now in the same
    // context as the database call
    Set<Id> recordIds = getAccountIds();
    return Database.getQueryLocator(query);
}

Instead of passing the identifiers of the records into your query builder method, have them in your method that actually calls the Database method and it will work because they are in the same context. This way Apex can merge your ids into the query properly (and of course: in this case the variable is declared in the same context).

9
  • I sort of don't follow, I'm passing the set of Ids into the batch class, You're doing something different are you saying that I need to requery for the Ids that I passed in? Commented Feb 18, 2022 at 20:59
  • Renato, I really liked your idea as an explanation, but is it the problem in Dan's case? He has the oppySet variable both in the scope of the method that returns the query String and the method that does the Database.query - luckily with the same name. Have I missed something? Commented Feb 19, 2022 at 8:19
  • @DanWooding what I'm saying is that you should not try to merge your ids into the query string in a context different from where you are calling the Database.getQueryLocator method. You don't need to requery, you just need to use them in the same scope. Commented Feb 19, 2022 at 21:11
  • @FelixvanHove He doesn't. The Database.getQueryLocator is called from a context where the set does not exist. As such, the ids on the set are not merged with when the query string is evaluated and executed. Commented Feb 19, 2022 at 21:11
  • @RenatoOliveira Why does the instance variable oppySet (line 3 of his code) not exist when getQueryLocator is called? Commented Feb 20, 2022 at 10:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .