0

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.

3 Answers 3

4

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));
}
6
  • one thing I forgot to mentioned I just updated my question please have a look
    – Nick
    Jul 20, 2017 at 13:55
  • You can also add group field into criteria class and generate SOQL WHERE per group, and join in the end to get following -- WHERE (a and b and ... and i) OR (j and k and ... and x) OR ...
    – kurunve
    Jul 20, 2017 at 14:13
  • hmm I'm not sure how this will work for dozens of fields public Criteria(String f_name, String f_value) { so I have to repeat dozens of time (in my case) Criteria c1 = new Criteria('empNum__c', ex.empNum__c);
    – Nick
    Jul 20, 2017 at 14:20
  • you can do it dynamically, f.e. get all fields from fields set and perform a loop based on your fields. f.e List<String> field_names = new List<String>{'f1', 'f2'}; for(String f : field_names) { criterias.add(new Criteria(f, (String)object.get(f));
    – kurunve
    Jul 20, 2017 at 14:21
  • IsFieldValueGiven should return a boolean, but personally I would change the method to isReady or similar and check that all the object's fields are populated, since they are all required for getSOQLCriteria. If someone just wants to check whether the FieldValue is populated, String.isNotBlank(single_criteria.FieldValue) is perfectly descriptive. Jul 20, 2017 at 14:32
1

Try this:

Map<String, String> whereFields = new Map<String, String>( 
    'empNum__c'  => object.empNum__c,
    'empCode__c' => object.empCode__c
    // ... other fields
);

String q = ' SELECT Id, Name, ' 
           + String.join( whereFields.keySet(), ',' ) 
           + ' FROM employee__c WHERE ';

Boolean isFirstCondition = true;
for ( String fieldName : whereFields.keySet() ) {
    if ( !isFirstCondition ) {
        q += ' AND ';
        isFirstCondition = false;
    }

    String fieldValue = whereFields.get( fieldName );
    if ( String.isNotBlank( fieldValue ) ) {
        q += fieldName + ' = \'' + String.escapeSingleQuotes(fieldValue.trim()) + '\' ';
    }
    else {
        q += fieldName + ' = null ';
    }
}
0

you can do like this also with simple logic ,

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

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

String Query='';

if(!String.isBlank(empNum)) {
        Query = Query + ' and Emp_Num__c = \''+String.escapeSingleQuotes(empNum.trim())+'\'';    
}
if(!String.isBlank(empCode)) {
        Query = Query + ' and Emp_Code__c = \''+String.escapeSingleQuotes(empCode.trim())+'\'';    
}

if(!String.isBlank(Query)) {
    Query = Query.substring(4, Query.length()) ; //removing Extra and before where condidtion
    Query='select id, name, empNum__c, empCode__c from employee__c where  ' + Query ;
   }
 else
  {
  Query='select id, name, empNum__c, empCode__c from employee__c' ;
  }

i Am not sure why used this condition string w = ' where (id != null and '; because normally when you retrieving object, Its ID never be null value.

You must log in to answer this question.

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