3

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

6

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;
}
  • 1
    Yup it was the quotations. There was a debug statement with the query although I missed the quotations. Odd they didn't include to mention that in the example code – TemporaryFix Mar 16 '16 at 20:33
  • 3
    Note: Make sure you're using String.escapeSingleQuotes, or you're setting yourself up for injection attacks. – sfdcfox Mar 16 '16 at 21:02
1

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.

  • value is prefixed in the string with a colon. baseQuery += key + ' = ' + :value; gives errors – TemporaryFix Mar 16 '16 at 20:27
  • 1
    @JoeyThomaschaske The value needs to be a variable available within the searchUsers method, or you need to include the actual value within the query. – Nick Cook Mar 16 '16 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.