[SalesForce] Bind variable with dotted name not working in dynamic SOQL

As mentioned in this answer, it's possible to use bind variables in dynamic SOQL queries. This seems like a great way to avoid SOQL-injection, and to build a query string once but use it multiple times with different values in the variables.

I'm working on a method that returns a list of objects. It accepts a class with multiple properties as its only parameter. The properties of this class are used to filter the results to just the required records. It will eventually be driven by a Visualforce component, which will be configured by querystring parameters in the URL.

I just ran into an issue where the query fails when I use dotted bind variables. If I copy the constructed dynamic SOQL into a static query, it works fine. This may be a bug, because in doing tests I found that :foo.bar would actually try to use local variable bar.

Here's one of my queries, which uses related custom objects. I've stripped it down to just the part that's failing (wrapped for legibility):

result = Database.query('SELECT Name, Another_Object__r.Name,
     Another_Object__r.Custom_Field__c
     FROM Custom_Object__c 
     WHERE Another_Object__r.Custom_Field__c = :filter.deviceType');

The error reported is System.QueryException: Variable does not exist: filter.deviceType. As mentioned above, the query works fine as static SOQL, just not dynamic:

result = [SELECT Name, Another_Object__r.Name, Another_Object__r.Custom_Field__c
FROM Custom_Object__c WHERE Another_Object__r.Custom_Field__c = :filter.deviceType];

It also works in this case:

String deviceType = filter.deviceType;
Database.query('SELECT Name, Another_Object__r.Name, Another_Object__r.Custom_Field__c
FROM Custom_Object__c WHERE Another_Object__r.Custom_Field__c = :deviceType');

I haven't read about this being a known limitation. I don't know if I should be using some other syntax/notation in the query — the dotted notation appears correct.

Time to report a bug? Thoughts on workarounds other than creating local variables to stand in for each member of the class?

Best Answer

You found the answer, dynamic SOQL is looking for your variable declaration within the scope of your method. I don't know why it doesn't respect public properties of the class, but that is the case and your solution of setting it to a method-scoped variable should do the trick.

Related Solutions

[SalesForce] How to execute a dynamic SOQL query with a variable number of “in” terms

This is the brute-force code my colleague ended up writing; not only multiple named bind fields but the fields needed to be of the right type:

public with sharing class DynamicQuery {

    private Set<String> aString = new Set<String>();
    private Set<String> bString = new Set<String>();
    private Set<String> cString = new Set<String>();
    private Set<String> dString = new Set<String>();
    private Set<String> eString = new Set<String>();

    private Set<Decimal> aDecimal = new Set<Decimal>();
    private Set<Decimal> bDecimal = new Set<Decimal>();
    private Set<Decimal> cDecimal = new Set<Decimal>();
    private Set<Decimal> dDecimal = new Set<Decimal>();
    private Set<Decimal> eDecimal = new Set<Decimal>();

    private Set<Date> aDate = new Set<Date>();
    private Set<Date> bDate = new Set<Date>();
    private Set<Date> cDate = new Set<Date>();
    private Set<Date> dDate = new Set<Date>();
    private Set<Date> eDate = new Set<Date>();

    private Set<DateTime> aDateTime = new Set<DateTime>();
    private Set<DateTime> bDateTime = new Set<DateTime>();
    private Set<DateTime> cDateTime = new Set<DateTime>();
    private Set<DateTime> dDateTime = new Set<DateTime>();
    private Set<DateTime> eDateTime = new Set<DateTime>();

    private Set<Boolean> aBoolean = new Set<Boolean>();
    private Set<Boolean> bBoolean = new Set<Boolean>();
    private Set<Boolean> cBoolean = new Set<Boolean>();
    private Set<Boolean> dBoolean = new Set<Boolean>();
    private Set<Boolean> eBoolean = new Set<Boolean>();

    private String soql;
    private String[] fields;
    private List<String> whereStrings;

    public DynamicQuery(String[] fields, String sobType, SObject[] sobs) {
        this.fields = fields;
        soql = 'select Id, ' + String.join(fields, ', ') + ' from ' + sobType + ' where ';

        whereStrings = new List<String>();

        Map<String, Set<Object>> whereSets = buildWhereSets(sobs);
        Integer size = whereSets.size();
        List<String> whereFields = new List<String>();
        whereFields.addAll(whereSets.keySet());

        for (String s : whereSets.keySet()) {
            //create list from set of field values to get 1st object
            List<Object> whereListFieldValues = new List<Object>();
            whereListFieldValues.addAll(whereSets.get(s));
            //get type of first object to add to its type set
            Object o = whereListFieldValues[0];
            if (o != null) {
                if (o instanceof String) {
                    addStringSet(s, whereSets.get(s));
                }
                else if (o instanceof Decimal) {
                    addDecimalSet(s, whereSets.get(s));
                }
                else if (o instanceof Date) {
                    addDateSet(s, whereSets.get(s));
                }
                else if (o instanceof DateTime) {
                    addDateTimeSet(s, whereSets.get(s));
                }
                else if (o instanceof Boolean) {
                    addBooleanSet(s, whereSets.get(s));
                }
            }
        }

        soql += String.join(whereStrings, ' and ');
    }

    public SObject[] query() {
        return Database.query(soql);
    }

    private Map<String, Set<Object>> buildWhereSets(SObject[] changed) {
        Map<String, Set<Object>> whereSets = new Map<String, Set<Object>>();
        for (String field : fields) {
            Set<Object> inSet = new Set<Object>();
            for (SObject record : changed) {
                try {
                    inSet.add(record.get(field));
                }
                catch (SObjectException e) {
                    throw new DuplicateException(
                            '"' + field + '"'
                            + ' is an invalid field name, please change your Extras'
                            + ' Duplicate Blocker custom settings to contain a valid field.'
                    );
                }
            }
            whereSets.put(field, inSet);
        }
        return whereSets;
    }

    private void addStringSet(String field, Set<Object> objectSet) {
        Set<String> stringSet = new Set<String>();
        for (Object o : objectSet) {
            stringSet.add((String)o);
        }

        if (aString.isEmpty()) {
            aString.addAll(stringSet);
            whereStrings.add(field + ' in :aString');
        }
        else if (bString.isEmpty()) {
            bString.addAll(stringSet);
            whereStrings.add(field + ' in :bString');
        }
        else if (cString.isEmpty()) {
            cString.addAll(stringSet);
            whereStrings.add(field + ' in :cString');
        }
        else if (dString.isEmpty()) {
            dString.addAll(stringSet);
            whereStrings.add(field + ' in :dString');
        }
        else if (eString.isEmpty()) {
            eString.addAll(stringSet);
            whereStrings.add(field + ' in :eString');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case String. '
               + 'The 6th String field is ' + field + '.'
           );
        }
    }

    private void addDecimalSet(String field, Set<Object> objectSet) {
        Set<Decimal> decimalSet = new Set<Decimal>();
        for (Object o : objectSet) {
            decimalSet.add((Decimal)o);
        }

        if (aDecimal.isEmpty()) {
            aDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :aDecimal');
        }
        else if (bDecimal.isEmpty()) {
            bDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :bDecimal');
        }
        else if (cDecimal.isEmpty()) {
            cDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :cDecimal');
        }
        else if (dDecimal.isEmpty()) {
            dDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :dDecimal');
        }
        else if (eDecimal.isEmpty()) {
            eDecimal.addAll(decimalSet);
            whereStrings.add(field + ' in :eDecimal');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Decimal. '
               + 'The 6th Decimal field is ' + field + '.'
           );
        }
    }
    private void addDateSet(String field, Set<Object> objectSet) {
        Set<Date> dateSet = new Set<Date>();
        for (Object o : objectSet) {
            dateSet.add((Date)o);
        }

        if (aDate.isEmpty()) {
            aDate.addAll(dateSet);
            whereStrings.add(field + ' in :aDate');
        }
        else if (bDate.isEmpty()) {
            bDate.addAll(dateSet);
            whereStrings.add(field + ' in :bDate');
        }
        else if (cDate.isEmpty()) {
            cDate.addAll(dateSet);
            whereStrings.add(field + ' in :cDate');
        }
        else if (dDate.isEmpty()) {
            dDate.addAll(dateSet);
            whereStrings.add(field + ' in :dDate');
        }
        else if (eDate.isEmpty()) {
            eDate.addAll(dateSet);
            whereStrings.add(field + ' in :eDate');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Date. '
               + 'The 6th Date field is ' + field + '.'
           );
        }
    }
    private void addDateTimeSet(String field, Set<Object> objectSet) {
        Set<DateTime> dateTimeSet = new Set<DateTime>();
        for (Object o : objectSet) {
            dateTimeSet.add((DateTime)o);
        }

        if (aDateTime.isEmpty()) {
            aDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :aDateTime');
        }
        else if (bDateTime.isEmpty()) {
            bDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :bDateTime');
        }
        else if (cDateTime.isEmpty()) {
            cDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :cDateTime');
        }
        else if (dDateTime.isEmpty()) {
            dDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :dDateTime');
        }
        else if (eDateTime.isEmpty()) {
            eDateTime.addAll(dateTimeSet);
            whereStrings.add(field + ' in :eDateTime');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case DateTime. '
               + 'The 6th DateTime field is ' + field + '.'
           );
        }
    }
    private void addBooleanSet(String field, Set<Object> objectSet) {
        Set<Boolean> booleanSet = new Set<Boolean>();
        for (Object o : objectSet) {
            booleanSet.add((Boolean)o);
        }

        if (aBoolean.isEmpty()) {
            aBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :aBoolean');
        }
        else if (bBoolean.isEmpty()) {
            bBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :bBoolean');
        }
        else if (cBoolean.isEmpty()) {
            cBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :cBoolean');
        }
        else if (dBoolean.isEmpty()) {
            dBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :dBoolean');
        }
        else if (eBoolean.isEmpty()) {
            eBoolean.addAll(booleanSet);
            whereStrings.add(field + ' in :eBoolean');
        }
        else {
           throw new DuplicateException(
               'Cannot match more than 5 fields of any one type - in this case Boolean. '
               + 'The 6th Boolean field is ' + field + '.'
           );
        }
    }
}

[SalesForce] Force Dynamic Soql results to include fields with null value

For me too, this behavior caused a lot of trouble.

I found not much clear documentation, but my guess is that the omission of null values is an optimization strategy of salesforce. In most cases this omission has no impact, but in lots of cases it saves memory as a very limited resource of the platform.

After some research best thing I got are the answers here: Apex JSON.serialize() with null values (RELOADED) - which cleard the backgrounds but actually didn't solve my problems.

Now my workarounds for these issues are

either to use schema methods to generate a list of all fields
or generate a list of fields (to be selected) manually

In both cases I have a List<String> of field-names. Then I create the SOQL string (from the list), execute my query and receive the sparse results.

Now I still have the field-list created before. Depending on the use case I either use this list for iterations in Apex together with SObject.get() or if I need it for JSON I render the fields as a second JSON string and let the JSON-consuming logic (in my case mostly javascript) iterate over the field-list with lookups to the result-list instead of using the result-list directly.

This pattern helped me to solve all the challenges I encountered so far and my first strong displeasure with the sparse results now changed and I actually even like them. For huge results you'll probably get a more efficient solution with sparse results instead of using full populated results.

Best Answer

Related Solutions

[SalesForce] How to execute a dynamic SOQL query with a variable number of “in” terms

[SalesForce] Force Dynamic Soql results to include fields with null value

Related Topic