[SalesForce] Building dynamic query string where ‘AND’ clause concatenation

EDIT:
One thing I forgot to mentioned is that, there is a condition what fields to be added to WHERE clause for an example:

Match1 = the where clause should be `emp_name__c, emp_num, emp_code`
Match2 = the where clause should be `emp_name__c, emp_inv_date__c`
......
......
.....

END EDIT

Building dynamic querystring on the basis of some conditions and even when it does not satisfies any condition, the query should be able to run properly, I have the following code but this code breaks when the field is null.

I have dozens of fileds but just for the clarity purpose I'm showing you only two fields here:

public string empNum {get;set;}
public string empCode {get;set;}

empNum = object.empNum__c;
empCode = object.empCode__c;

string empNumWhere = '';
string empCodeWhere = '';

if(!String.isEmpty(empNum)) {
        empNumWhere = 'Emp_Num__c = \''+String.escapeSingleQuotes(empNum.trim())+'\'';    
}
if(!String.isEmpty(empCode)) {
        empCodeWhere = 'Emp_Code__c = \''+String.escapeSingleQuotes(empCode.trim())+'\'';    
}

string q = ' select id, name, empNum__c, empCode__c from employee__c';
string w = ' where (id != null and ';
w += empNumWhere + ' and ' + empCodeWhere + ')';

not always I will have the value for both empNum and empCode so I'm getting the null added to the query hence, it breaks the soql

Is there away I can improve the above code and I have like dozens of fields that I'm adding dynamic and I have to check all for null before have a final query.

Best Answer

I can suggest you to create criteria pseudo class, and generate as much criterias as you want from static or dynamic dataset based on your scenario, Please, check following pseudocode

//Helper Class
public class Criteria {
   public String FieldName;
   public String Operator;
   public String FieldValue;
   public Criteria(String f_name, String f_value) {
       FieldName = f_name;
       FieldValue = f_value;
       Operator = '=';
   }
   public String getSOQLCriteria() {
       return fieldName + ' '  + Operator + ' \'' + FieldValue + '\'';
   }

   public String IsFieldValueGiven() {
       return FieldValue != null && FieldValue != '';
   }
}

public String generateWhereQuery(List<Criteria> criterias) {
    List<String> and_conditions = new List<String>();
    for(Criteria single_criteria : criterias) {
        if (single_criteria.IsFieldValueGiven()) {
            and_conditions.add(single_criteria.getSOQLCriteria());
        }
    }
    return String.join(and_conditions, ' AND ');
}


//Actual Code
Criteria c1 = new Criteria('empNum__c', ex.empNum__c);
Criteria c2 = new Criteria('empCode__c', ex.empCode__c);

String where_cond = generateWhereQuery(new List<Criteria>{c1, c2});
String soql = 'SELECT Id FROM Nice_Object__c WHERE ' + where_cond;

So, in case if you have a lot of fields to add to soql query, you just create List of Criteria records on demand, use them in other places (f.e., Visualforce Page or as input for Lightning controller) and build dynamic SOQL based on that. If case of dynamic list of fields, it will be similar to next:

List<String> field_names = new List<String>{'empNum__c', 'empCode__c'};
List<Criteria> all_criterias = new List<Criteria>();
for(String field_name : field_names) {
   all_criterias.add(new Criteria(field_name, (String) sobject.get(field_name));
}
Related Topic