Can Apex SOQL query variable binding be done in a method other than the method which invokes Database.query?

Question

Apex Commons has some brilliant solutions, but one of the things which I feel is missing is an object-oriented approach to the SOQL query's "WHERE" conditions.

I'm presently working on my own framework, of which I freely admit the Selector code is heavily derived from Apex Commons.

I'd like to include an OOP way of constructing the "WHERE" clause.

To this end, I've created an interface:

public interface DAO_WHERE_ClauseIntf { 
    String toSOQL();
}

and a few implementations of the interface.

The problem is with this implementation:

public class DAO_WHERE_VariableComparisonClause implements DAO_WHERE_ClauseIntf {
    DAO_WHERE_ComparisonOperatorEnumHelper comparisonHelper;

    String fieldName;
    String comparisonOperator;
    Object value;

    public DAO_WHERE_VariableComparisonClause (
            SObjectField sObjectField,
            DAO_WHERE_ComparisonOperatorEnum comparisonOperator,
            Object value
        ) {
        this.fieldName = DAO_QueryFactoryFieldHelper.getInstance().getFieldTokenPath(sObjectField);
        this.comparisonOperator = DAO_WHERE_ComparisonOperatorEnumHelper.getInstance().get(comparisonOperator);
        this.value = value;
    }

    public String toSOQL() {
        Object someValue = this.value; 
        return fieldName  
            + comparisonOperator
            + ':someValue';      
    }
}

Here, the variable is being bound in a different method than the method which actually executes Database.query(), so the variable is out of scope and the query dies with:

|FATAL_ERROR|System.QueryException: Variable does not exist: someValue

I have made an implementation which works:

public class DAO_WHERE_BindingComparisonClause implements DAO_WHERE_ClauseIntf {
    DAO_WHERE_ComparisonOperatorEnumHelper comparisonHelper;

    String fieldName;
    String comparisonOperator;
    String bindVariableName;

    public DAO_WHERE_BindingComparisonClause (
            SObjectField sObjectField,
            DAO_WHERE_ComparisonOperatorEnum comparisonOperator,
            String bindVariableName
        ) {
        this.fieldName = DAO_QueryFactoryFieldHelper.getInstance().getFieldTokenPath(sObjectField);
        this.comparisonOperator = DAO_WHERE_ComparisonOperatorEnumHelper.getInstance().get(comparisonOperator);
        this.bindVariableName = bindVariableName;
    }

    public String toSOQL() { 
        return fieldName  
            + comparisonOperator
            + ':' + bindVariableName;       
    }
}

But the problem with this implementation is I need to include the name of some variable which will be in scope at the time Database.query() is executed, which isn't a very safe approach and may limit my flexibility to construct queries with any particular instances.

I was considering possibly including the DAO_WHERE_VariableComparisonClause's value directly in the String, but then:

1. I'm not sure how literal values should look in dynamic SOQL queries, especially if the value is a collection.

2. I'm not sure how long SOQL queries can be - what happens if I have a set which includes 200 values?

What is the best way to make DAO_WHERE_VariableComparisonClause work as expected?

Answer 1

Since @crmprogdev offered a more general direction to pursue, I wanted to cover some of the details here.

SOQL Dynamic Binding

While I'm not sure this is formally documented, and hence there may be unusual edge cases, the binding of values takes place in the scope where Database.query() is called with a Dynamic SOQL query string, as you've observed in the negative case. It does work fine if you can guarantee that the variable in question will be in scope, but this typically works better for purely internal private methods than public APIs.

public class TestSOQLBinding {
    public static String getQuery() {
        String name = 'Confounding Name';
        return 'SELECT Id, Name FROM Account WHERE Name = :name';
    }

    public static List<Account> queryAccountWithName(String name) {
        return Database.query(TestSOQLBinding.getQuery());
    }
}

If you go into Anonymous Apex and issue System.debug(TestSOQLBinding.queryAccountWithName('myRealAccountName'));, what you'll get back is the real account - not "Confounding Name". So the binding is done in the local namespace of queryAccountWithName().

You will receive a QueryException at runtime if there is no matching variable in scope.

Literal Formats

There are specific formats that are required for different literal types, of which this is a non-exhaustive list. Note that simple concatenation of a string value with a non-string value (myStringValue + myDateValue) does not work in the general case for appending a literal value to a query that is being constructed. Rather, the non-string value should be explicitly converted to the appropriate literal format for its type before being included in the dynamic query as a string.

• Strings have to be single-quoted, and hence must be escaped using String.escapeSingleQuotes().
• Ids have to be single-quoted, but can be 15 or 18 characters.
• Dates must not be quoted, and must be in YYYY-MM-DD format (ISO). Note that String.valueOf(someDate) gives the right format, but someString + someDate does not.
• There are three acceptable formats for DateTime values.
• Collections with IN are wrapped in ( and ), with commas between values. Often this can be generated with '(' + String.join(literalValues, ', ') + ')'. The values that are part of the collection must be formatted like any other literals of their type (strings must be quoted, etc).

Query Length Limitations

• The total length of the query must be less than 20,000 characters.
• The total length of the WHERE clause must be less than 4,000 characters.

You'll get an exception if you breach those limits.

Answer 2

I don't know that this will entirely answer your question, but it may at least point you in the "right" direction. A different approach that @andrewfawcett takes is to begin by using a Selector class along with a String Builder class, to incrementally build what's needed to finally make the call to his QueryFactory class as described in his book Force.com Enterprise Architecture.

Andy makes extensive use of Factory Patterns in a lot of what he does. I'd highly recommend you take a look at these classes and their architecture. I've implemented the Financial Force fflibs in a production org and can tell you that using this separation of concerns approach as described in Andy's book works quite well as does the query factory I've linked you to.