[SalesForce] Forming a dynamic SOQL with multiple fields in WHERE clause

I have written a method to dynamically query an object and its fields.
As I want to keep it configurable, a fieldset is used to provide with which fields to query, and also the object.
The problem I am facing is in the WHERE clause.

My objective is to filter the query with multiple columns. For this, I pass a map which will contain values in the following format:

Map<'Email', <Set of emails to filter>,
    'ID', <Set of Ids to filter with>>

Following code fills the fieldVariableFilter:

 Map < String, List < Object >> fieldVariableFilter = new Map < String, List < Object >> ();
  List<Object> tempObjList = new List<Object>();
  fieldVariableFilter.put('someIdField', null);
  fieldVariableFilter.put('SomeEmailField', null);



  // Filter the incoming leads into a map, and if a corresponding Contact record exists w.r.t. to the email, map it
  for (Lead eachLead: incomingLeads) {

  if (eachLead.someIdField!= NULL && eachLead.someIdField!= '') {
    tempObjList = fieldVariableFilter.get('someIdField');
    tempObjList.add(eachLead.someIdField);
    fieldVariableFilter.put('someIdField',tempObjList);
   }
  if (eachLead.SomeEmailField!= NULL && eachLead.SomeEmailField!= '') {
    tempObjList = fieldVariableFilter.get('SomeEmailField');
    tempObjList.add(eachLead.SomeEmailField);
    fieldVariableFilter.put('SomeEmailField',tempObjList);
   }


  }

Currently, the following code only works for 1 key-value pair. This is because the "collectionVariable" will always be the same for each iteration, and I don't know how to dynamically bind, each collection with its corresponding field name.

public List < SObject > formQueryAndGetObject(List < Schema.FieldSetMember > objFields, String objToQuery, Map < String, List < Object >> filterFieldAndVariable) {
  String query = 'SELECT ';
  for (Schema.FieldSetMember f: objFields) {
   query += f.getFieldPath() + ', ';
  }
  query += 'Id FROM ' + objToQuery;
  query += ' WHERE ';
  // The below filter needs improvement|| it will currently only work for one key value pair
  if (filterFieldAndVariable != NULL && !filterFieldAndVariable.isEmpty()) {
   for (String eachKey: filterFieldAndVariable.keySet()) {
    List < Object > collectionVariable = filterFieldAndVariable.get(eachKey);
    query += eachKey + '=: collectionVariable ';
    query += 'OR '
   }
   query = query.removeEnd('OR ');
  }
  System.debug('--queryformed-->' + query);

  return Database.query(query);
 }

Best Answer

The simplest and most obvious path is to build the collections as literals, rather than variable bindings. For example, if your email collection contained foo@example.test and bar@example.test, you could filter:

WHERE Email__c IN ('foo@example.test', 'bar@example.test')

This approach to filtering will allow you to include an arbitrary number of collections, and they do not require concrete bindings.

If all of your collections can be cast to strings, this approach will be greatly simplified. Change your structure to Map<String, Set<String>>(), then your literal clause building might look something like:

public String buildWhereClause(Map<String, List<String>> filters)
{
    return String.join(buildSubclauses(filters));
}
public static List<String> buildSubclauses(Map<String, List<String>> filters)
{
    List<String> filters = new List<String>();
    for (String field : filters.keySet())
        filters.add(buildSubclause(field, filters.get(field));
    return filters;
}
public static String buildSubclause(String field, List<String> values)
{
    Set<String> sanitizedValues = new Set<String>();
    for (String value : values) filters.add(sanitize(value));
    return String.escapeSingleQuotes(field) + ' IN (' +
        String.join(new List<String>(sanitizedValues), ',') +
    ')';
}
public static String sanitize(String value)
{
    return String.isBlank(value) ? 'null' :
        '\'' + String.escapeSingleQuotes(value) + '\'';
}

It should be obvious how much more complicated this approach might grow if you need to support other field types. I explored this approach (and the more complicated version which does support all primitive field types) in a FluentQuery library I wrote, and specifically you can look at its ValueFormatter to learn more.

Related Topic