[SalesForce] query sObject or Custom setting by multiple fields

i need to implement logic at couple of places which is like querying an sObject or a custom setting. There are input fields using which i need to create filter criteria to reach appropriate records in object or setting.

Many SOQL queries i use are like :

Select col_a,col_b, col_c from object_or_custom_setting_name WHERE col_a = :binc_var_a AND col_b = :bind_var_b AND col_c= :bind_Var_c

i capture values of bind variables from values earlier entered by user.

the problem is that users do not always capture data in all of these fields. and these are not required fields. so i want to apply AND condition for specific column only when user has entered value in corresponding input column.
Id value entered by user for bind_Var_c is blank i dont want to put the last AND clause in WHERE.
is there a way to achieve such requirement without writing SOQL and similar code again and again in the code.

Best Answer

As I understand your question, you need to dynamically generate your where clause based on the inputs. This is possible using dynamic SOQL - constructing your query as a string, dynamically. First a code example based on your requirements, and then some important notes below.

list<string> criteria = new list<string>();

if (bind_var_a!=null) {
  criteria.add('col_a = :bind_var_a')
}

if (bind_var_b!=null) {
  criteria.add('col_b = :bind_var_b')
}

if (bind_var_c!=null) {
  criteria.add('col_c = :bind_var_c')
}

string whereClause = '';
if (criteria.size()>0) {
  whereClause = ' where ' + String.join(criteria, ' AND ');
}

string soql = 'select col_a, col_b, col_c from object_or_custom_setting_name ' + whereClause;
list<object_or_custom_setting_name> results = Database.query(soql);

The logic is simple - check each input field, if it is not null, add a test for that field to a list of criteria strings; if we have any criteria, assemble them into a where clause.

There is an important caveat here: when using :bind variables in a string with Database.query(), the variables must be simple basic types in local scope. Static variables, class properties, etc, will not work. So for example, if your input fields are passed in as properties of another object, you would need to extract them into local variables first:

//if params is an object with fields, this won't work:
if (params.bind_var_a!=null) {
  criteria.add('col_a = :params.bind_var_a'); //WONT WORK!
}

//Instead, use a local variable:
string bind_var_a = params.bind_var_a;
if (bind_var_a!=null) {
  criteria.add('col_a = :bind_var_a')
}

Likewise, if you want to re-use this query in your code, don't build a method that returns the soql string; build a method that runs the query and returns the list of SObjects, so that the query runs in the same scope where the bind variables are declared.

Related Topic