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

4 Answers 4

2

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

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;
}
2
  • 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 Mar 16, 2016 at 20:33
  • 4
    Note: Make sure you're using String.escapeSingleQuotes, or you're setting yourself up for injection attacks.
    – sfdcfox
    Mar 16, 2016 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.

2
  • value is prefixed in the string with a colon. baseQuery += key + ' = ' + :value; gives errors Mar 16, 2016 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 C
    Mar 16, 2016 at 20:30
0
public class QueryContact {
    public static Id getContactID(String lastName, String title) {
        try {
            Contact myContact = [SELECT Id FROM Contact WHERE LastName = :lastName AND Title = :title LIMIT 1];
            return myContact.Id;
        } catch (Exception ex) {
            return null;
        }
    }

    public static Id getContactIDWithBinds(Map<String, Object> bindVars) {
        // do not modify any code above this line
        // implement the logic that will use bindVars to retrieve the contact's ID

        String queryString = 'SELECT Id FROM Contact WHERE LastName = :lastName AND Title = :title LIMIT 1';

        try {
            // Ensure that the bind variables contain the necessary keys
            if (bindVars.containsKey('lastName') && bindVars.containsKey('title')) {
                List<Contact> contacts = (List<Contact>)Database.queryWithBinds(queryString, bindVars, AccessLevel.USER_MODE);
                if (contacts != null && !contacts.isEmpty()) {
                    // Assuming you want the first contact's ID if there are matches
                    return contacts[0].Id;
                } else {
                    // Handle the case when no matching contacts are found
                    return null;
                }
            } else {
                // Handle the case when required bind variables are not provided
                return null;
            }
        } catch (Exception ex) {
            // Handle exceptions, e.g., if the query fails
            return null;
        }
    }
}
1
  • 2
    7 years ago someone asked a question, and it got an accepted answer. So what use is your code? If you don't write some words, why would anyone want to bother to look into your code? Dec 19, 2023 at 7:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .