Got error while constructing the dynamic query

Question

I was trying to build a query to pass it in the QueryLocator. The following is the code snippet I tried to generate the dynamic query,

public String buildQueryString(Object_Rule__mdt ObjectRule, Datetime createdDatetime) {
    List<String> fields = new List<String>();
    Set<String> statusSetStrings = new Set<String>{'Pending', 'Processing'};
    this.query = 'SELECT ';
    for (Field_Rule__mdt fieldRules : ObjectRule.Field_Rules__r) {
        fields.add(fieldRules.Field_Name__c);
    }

    this.query += String.join(fields, ', ') + ', ' + ContactIdField__c + ' FROM ' + ObjectRule.SObject__c + ' WHERE ' + ObjectRule.ContactIdField__c
            + ' IN (SELECT Contact__c FROM Request__c WHERE Status__c IN :statusSetStrings AND CreatedDate < :createdDatetime)';

    return this.query;
}

When I tried the above query in a Batch then the batch is returned the error as 'Variable does not exist :statusSetStrings' I was trying to change the query as without the ':' as well before statusSetStrings.

What did I missed?

Answer 1

When you use Apex binding in a dynamic query, you need to think about the idea of scope: where is a variable name visible? In what scope does it exist? Let's look at just the skeleton of your code:

public String buildQueryString(Object_Rule__mdt ObjectRule, Datetime createdDatetime) {
    Set<String> statusSetStrings = new Set<String>{'Pending', 'Processing'};
    this.query = 'SELECT ';
    this.query += String.join(fields, ', ') + ', ' + ContactIdField__c + ' FROM ' + ObjectRule.SObject__c + ' WHERE ' + ObjectRule.ContactIdField__c
            + ' IN (SELECT Contact__c FROM Request__c WHERE Status__c IN :statusSetStrings AND CreatedDate < :createdDatetime)';

    return this.query;
}

Notice that statusSetStrings has local scope - it's a variable declared in this method, and it goes out of scope and no longer exists after the closing bracket of the method.

Why does that make a difference? It's because when you construct and return this query, you haven't yet invoked the "magic" part of Apex bindings. Right now, this.query is just a String that contains the substring :statusSetStrings. That substring has no special meaning to Apex until you actually issue the dynamic query with Database.query().

Once you do perform the query, all of those variable bindings are looked up in the local scope where the query is executed - not where it is constructed. Put shortly, statusSetStrings has to be in scope where the query is executed.

An easy fix, since statusSetStrings is constant, is to simply move it to an instance variable within this class (assuming that the code that does issue this dynamic query is in another method of the class).

Answer 2

    public String buildQueryString(Object_Rule__mdt ObjectRule, Datetime createdDatetime) {
      List<String> fields = new List<String>();
      Set<String> statusSetStrings = new Set<String>{'Pending', 'Processing'};
      this.query = 'SELECT ';
      for (Field_Rule__mdt fieldRules : ObjectRule.Field_Rules__r) {
        fields.add(fieldRules.Field_Name__c);
      }

      this.query += String.join(fields, ', ');
      this.query += ', ' + ContactIdField__c;
      this.query += ' FROM ' + ObjectRule.SObject__c;
      this.query += ' WHERE ' + ObjectRule.ContactIdField__c + ' IN ';
      this.query += '(SELECT Contact__c FROM Request__c'; 
      this.query += ' WHERE Status__c IN (' + String.join(statusSetStrings, ', ') + ') ';
      this.query += ' AND CreatedDate < :createdDatetime)';

    return this.query;
}

Your dynamic query can't 'see' the bind variable you're trying to reference . You should parse your set of strings into the query string instead. Something like the above snippet should work (string assembly split for ease-of-reading).