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
andbar@example.test
, you could filter: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: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 itsValueFormatter
to learn more.