Binding variables in dynamic soql

Question

I am trying to dyanically attach a WHERE clause to a base query string by following this guide.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm

I am running into an issue where I try to reference a variable and I either get System.QueryException: expecting a colon, found 'Test' or System.QueryException: Variable does not exist: value method that generates the WHERE clause

private static String constructWhereClause(String baseQuery, Map<String,String> whereMap){
    if(whereMap.size() > 0){
        baseQuery += ' WHERE ';
        Integer i = 0;
        for(String key: whereMap.keySet()){
            String value = whereMap.get(key);
            baseQuery += key + ' = :value'; // or baseQuery += key + ' = ' + value; both are shown as examples in the link
            if(i < whereMap.size() -1){
                baseQuery += ' AND ';
            } 
            ++i;
        }
    }
    return baseQuery;
}

Method that executes queries

public static List<User> searchUsers(Boolean isActive, String email, String lastName, String role, Id standardProfileId, Id adminProfileId){
    String query = 'SELECT Id, firstName, lastName, email, IsActive, profileId FROM User';
    Map<String, String> whereMap = new Map<String, String>();
        if(lastName != null && lastName.length() > 0){
            whereMap.put('lastName', lastName);
        }
        if(email != null && email.length() > 0){
            whereMap.put('email', email);
        }
        if(isActive != null){
            whereMap.put('isActive', String.valueOf(isActive));
        }
        if(role != null){
            Id profileId;
            if(role == 'Standard'){
                profileId = standardProfileId;
            }else{
                profileId  = adminProfileId;
            }
            whereMap.put('profileId', profileId);
        }
        System.debug('Query Before Where ' + query);
        query = constructWhereClause(query, whereMap);
        System.debug('Query After Where ' + query);
        List<User> results = Database.query(query);
        System.debug('Results: ' + results);
        return results;
}

Sample Queries SELECT Id, firstName, lastName, email, IsActive, profileId FROM User WHERE lastName = :value

SELECT Id, firstName, lastName, email, IsActive, profileId FROM User WHERE lastName = Test

Answer 1

Starting from Spring'23, this problem now has the native solution:

Map<String, Object> acctBinds = new Map<String, Object>{'acctName' => 'Acme Corporation'};
List<Account> accts = 
    Database.queryWithBinds(
        'SELECT Id FROM Account WHERE Name = :acctName', 
        acctBinds, AccessLevel.USER_MODE);

new methods are: Database.queryWithBinds, Database.getQueryLocatorWithBinds, and Database.countQueryWithBinds

References: https://help.salesforce.com/s/articleView?id=release-notes.rn_apex_bind_var_soql.htm&type=5&release=242
https://developer.salesforce.com/docs/atlas.en-us.242.0.apexref.meta/apexref/apex_methods_system_database.htm#apex_System_Database_queryWithBinds

Answer 2

If you are building the Query String dynamically you don't need to bind the variable, just append the value.

I think you would probably have figured this one out if you were to simply debug your query string.

<!-- language: lang-apex -->

private static String constructWhereClause(String baseQuery, Map<String,String> whereMap){
    if(whereMap.size() > 0){
        baseQuery += ' WHERE ';
        Integer i = 0;
        for(String key: whereMap.keySet()){
            String value = whereMap.get(key);
            //baseQuery += key + ' = :value'; // or baseQuery += key + ' = ' + value; both are shown as examples in the link
            //Shouldn't work as value is appended as its value
            baseQuery += key + ' = :' + value;
            //This should work
            baseQuery += key + ' = \'' + value + '\'';
            if(i < whereMap.size() -1){
                baseQuery += ' AND ';
            } 
            ++i;
        }
    }
    return baseQuery;
}

Answer 3

SELECT Id, firstName, lastName, email, IsActive, profileId FROM User WHERE lastName = Test

Should be either:

SELECT Id, firstName, lastName, email, IsActive, profileId FROM User WHERE lastName = :Test

OR

SELECT Id, firstName, lastName, email, IsActive, profileId FROM User WHERE lastName = 'Test'

Note the Test has to be either a variable prefixed with a colon, or a string surrounded by quotes.

The value needs to be a variable available within the searchUsers method.