I have an auraenabled method in my lightning controller apex class. My requirement is that when user selects a picklist value of status__c and/or mode_of_travel__c as a filter and click on apply then a table of expense records based on the filter should be displayed. The thing is the "if condition" in the where clause' does not seem to be working!

I am getting the following error :

"System.QueryException: unexpected token: 'Status__c'"

public static List<Expenses__c> findByName(String statuskey,string modekey,list<Expenses__c> explist) {
  List<Expenses__c> results = Database.query('SELECT Id,name FROM 
 Expenses__c where ID in:explist');
 Set<Id> recordIds = (new Map<Id,Expenses__c>(results)).keySet();

 String queryStr= 'SELECT Id, Name, Name__c, Reason__c, Amount__c, 
          Date_of_Expense__c, Status__c,'; 
    queryStr+= 'Mode_of_Travel__c, Category__c, Budget__c, Budget__r.Name,';
       queryStr+='Expense_owner__c, From_Date__c, To_Date__c, isclosed__c,';
        queryStr+='Budget_detail__c, Reimbursed__c FROM Expenses__c WHERE Id 

         if(statuskey!=null && statuskey!= '' )
         { queryStr+= 'AND Status__c =:statuskey';}

         else if(modekey!=null && modekey!='')
         { queryStr+= 'AND Mode_of_Travel__c =:modekey';}

          List<Expenses__c> e = Database.query( queryStr );

  system.debug('list of final filter exp:'+e);
  return e; 
  • 2
    spaces between " AND " are missing? – kurunve Jan 25 '18 at 6:45
  • Also - print the query to debug log and post it so we can see if it is ok: system.debug(queryStr); – Itai Shmida Jan 25 '18 at 7:02

As the comments said, you're missing a space. For this reason, when I'm building a list of filters, I always use a list of strings, and join them together via String.join.

String[] filters = new List<String>('Id = :recordIds');
if(String.isNotBlank(statuskey)) {
    filters.add('Status__c = :statuskey');
if(String.isNotBlank(modekey)) {
    filters.add('Mode_of_Travel__c = :modekey');
queryStr += ' WHERE ' + String.join(filters, ' AND ');

This allows you to easily add or remove filters later, and provides a consistent interface.

Alternatively, consider using fflib_QueryFactory or another pre-built solution so you don't have to keep "reinventing the wheel" each time you need to do something like this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.