function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lokesh Krishna SagiLokesh Krishna Sagi 

Generating dynamic soql with multiple where clauses

Hello everyone,
I am trying to form a dynamic soql with multiple where clauses like below..
 
public class SearchClass {

@AuraEnabled
public static List<Object__c> searchWithInputs(String fromDate, String toDate, List<String> statuses...) {

     List<Object__c> records = new List<Object__c>();

     String query = 'Select Id,..... from Object__c where ';
     .
     .
     .
     .
     .
     .
     records = Database.query(query);

     return records;

}
Here, the method 'searchWithInputs' have multiple input parameters coming from a lightning component client controller. I need to generate the soql query in such a way it will handle different scenario like,
  • All input params are not null (no problem with with this one)
  • All input params are null (no problem with with this one)
  • Only some params are coming and rest are nulls. 
How do I form the query to make it dynamic so it will leave out the inputs which are nulls and consider only not nulls (actual inputs)?

Thanks
Best Answer chosen by Lokesh Krishna Sagi
Bhargavi TunuguntlaBhargavi Tunuguntla
Hi Lokesh 

You can either check the as shown below:
  
List<Object__c> records = new List<Object__c>();

     String query = 'Select Id,..... from Object__c where id!=null';
    if (!string.isEmpty(fromDate))        
        {
       
          query+='and fromDate=' fromDate;
        }
	 if (!string.isEmpty(toDate))        
        {
       
         
        }
	 if (statuses.size()>0)        
        {
       
         
        }
	
     records = Database.query(query);

     return records;

or you can use conditional operator ,so that we dont require many if conditions:
  
List<Object__c> records = new List<Object__c>();

     String query = 'Select Id,..... from Object__c where id!=null';
     query+='fromDate='+((!string.isEmpty(fromDate))?fromDate:'');
     records =database.query(query);

Hope either of this will be useful.

Thanks
Bhargavi.​

All Answers

Lokesh Krishna SagiLokesh Krishna Sagi
Here, the input params are where clauses of the query
Bhargavi TunuguntlaBhargavi Tunuguntla
Hi Lokesh 

You can either check the as shown below:
  
List<Object__c> records = new List<Object__c>();

     String query = 'Select Id,..... from Object__c where id!=null';
    if (!string.isEmpty(fromDate))        
        {
       
          query+='and fromDate=' fromDate;
        }
	 if (!string.isEmpty(toDate))        
        {
       
         
        }
	 if (statuses.size()>0)        
        {
       
         
        }
	
     records = Database.query(query);

     return records;

or you can use conditional operator ,so that we dont require many if conditions:
  
List<Object__c> records = new List<Object__c>();

     String query = 'Select Id,..... from Object__c where id!=null';
     query+='fromDate='+((!string.isEmpty(fromDate))?fromDate:'');
     records =database.query(query);

Hope either of this will be useful.

Thanks
Bhargavi.​
This was selected as the best answer