Invalid bind expression type of ANY for column of type Decimal

Question

I know this question has been answered in few places my scenario is a bit different I think.

I'm building a dynamic SOQL query, the WHERE clause is going to be dynamic. I have a List<String> values in which values are stored from VF page using input fields and later this list is used to query records.

My VF page allows users to select fields (these fields will be added to the where clause) on a certain object that will allow them query records. So, Let's say a user selects Age__c field from the VF page which is of type Double and then the user fills in the input fields with Age values.

This is how the query will look like:

SELECT Id, Some_Field__c FROM Some_Object__c WHERE Age__c IN :values

This query fails to execute since the type of Age__c is Double and the collection passed as a filter is List<String>. I then converted this list to a List<Object> and that's when it throws the below error:

Invalid bind expression type of ANY for column of type Decimal

How can I typecast this values list to make this work? How can I solve this issue? Any help will be appreciated.

Answer 1

Apex's lack of runtime dynamism and reflection makes fully generic code a lot clumsier than in many languages. There's no reason you can't accomplish it here, though. I think a sketch of the solution would look like this.

We'd use the Describe API to obtain the type for each field that we want to include in the query. Then, based upon that type, we'd dynamically generate a WHERE clause with a series of IN operators as a String, converting and formatting our untyped Object values appropriately based on the required SOQL format for that data type.

Once the entire query has been constructed, we'd send it to Database.query().

String baseQuery = 'SELECT Id, Some_Field__c FROM Some_Object__c WHERE ';
List<String> clauses = new List<String>();

// Suppose we have a Map<SobjectField, Set<Object>> values 
// where the Set<Object> is the values we want to check for.

for (SobjectField f: values.keySet()) {
    // Determine the type of this field.
    Schema.SOAPType t = f.getDescribe().getSOAPType();
    String clause = f.getDescribe().getName() + ' IN (';
    List<String> items = new List<String>();

    // Not all SOAP types necessarily should be handled differently
    // Filling this out left as an exercise for the reader.

    if (t == Schema.SOAPType.Double) {
        // Convert the value and accumulate a String list
        for (Double d : (Set<Double>)values.get(f)) {
            items.add(String.valueOf(d));
        }
    } else if (t == Schema.SOAPType.String) {
        for (String s : (Set<String>)values.get(f)) {
            items.add('\'+String.escapeSingleQuotes(s) + '\'';
        }
    } // continue down the list of types, handling appropriately 
      // (or raising exceptions if needed). You may not *want* to handle
      // every single type of field.

    // Turn the string list into a clause...
    clause += String.join(items, ', ') + ')';
    // and add that clause to our list of clauses
    clauses.add(clause);
} 

// Note that we make no attempt here to check whether the query's length is allowable or that it doesn't breach any other limits.
baseQuery += String.join(clauses, ' AND ');

(Please note, the above is un-compiled and un-tested, but I think a solution along this pattern should be workable).