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
degmodegmo 

Help with Dynamic SOQL

Hello All,
I need to build a dynamic SOQL where clause based on 15 fields and wanted to see if anyone can recommend a more efficient approach than what I have below:
  
List<Drive__c> lstDrive = [SELECT Id, field1, field2, field3, field4, field5, field 6, field 7 FROM Drive__c where Id=:currentId LIMIT 1];

If(!lstDrive.isEmpty()){

Drive__c currentDrive = lstDrive[0];
String myQuery = 'SELET Id FROM Account Where ';

if(currentDrive.field1 != null) {
myQuery += 'field1 >= ' + currentDrive.field1 + ' AND ';
}
if(currentDrive.field2 != null) {
myQuery += 'field2 >= ' + currentDrive.field2 + ' AND ';
}
if(currentDrive.field3 != null) {
myQuery += 'field3 <=' + currentDrive.field3 + ' AND ';
}
.
.
.
.
}
List<Account> result = Database.query(myQuery);
Basically, I have 15 such 'if' blocks that build the where clause.  Any suggestions or recommendation would be greatly appreciated.
AnudeepAnudeep (Salesforce Developers) 
One recommendation is to build the dynamic soql query using a for loop. 
Basically, you run a for loop on the lstDrive and build the query

Please take a look at this post for sample code

Let me know if this helps, if it does, please mark this answer as best so that others facing the same issue will find this information useful. Thank you
ravi soniravi soni
Hi degmo,
before try following code we are assuming that in contact and account both have  same fields like Field_1_c,Field_2_c,... etc. 
public class BuildDynmicQuery {
    public static void dynmicSoqlCreate(string currentId,string Flds){
        string query = 'SELECT Id,' + Flds + ' FROM Contact where Id=: currentId   LIMIT 1';
        List<Contact> resultOfContact =Database.query(query);
        If(!resultOfContact.isEmpty()){
            Integer count=0;
            boolean isNotNull = true; 
            Contact con = resultOfContact[0];
            String myQuery = 'SELECT Id FROM Account';
            list<string> lstConFlds = Flds.split(',');
            for(integer i=0;i<lstConFlds.size();i++){
                      string conFld = string.valueOf(con.get(lstConFlds[i]));
       
                if(con.get(lstConFlds[i]) != null) {
                    if(isNotNull){
                        myQuery+= ' WHERE ';
                    }
                    else {
                       myQuery +=  ' AND ';  
                    }
                    isNotNull = false;
                    myQuery += lstConFlds[i] + '=: conFld';
                   
                }
            }
            system.debug('myQuery==> ' + myQuery);
           List<Account> AccResult = Database.query(myQuery);
            system.debug('AccResult==> ' + AccResult);
        }
        
    }
}

Testing Apex Code 
String Flds = 'Field_1__c,Field_2__c,Field_4__c,Field_5__c';
BuildDynmicQuery.dynmicSoqlCreate('0035g0000022AzkAAE', Flds);
let me know if it helps you and marking it as best.
Thank you
Francesco DessìFrancesco Dessì

If you use always the same fields and your clauses are all with AND, you could try something like that:

List<Contact> contacts = [SELECT Id, Account.Id, Account.Name, Account.CreatedDate FROM Contact LIMIT 1];

if(!contacts.isEmpty()) {
    String accountName = contacts[0].Account.Name;
    String accountId = contacts[0].Account.Id;
    Datetime accountCreatedDate = contacts[0].Account.CreatedDate;
    
    List<String> queryFields = new List<String>{'Id', 'Name'};
    List<String> whereClauses = new List<String>();
    String query = 'SELECT ' + String.join(queryFields, ', ') + ' FROM Account';
    
    if(accountId != null) {
        whereClauses.add('Id = :accountId');
    }

    if(accountName != null) {
        whereClauses.add('Name = :accountName');
    }

    if(accountCreatedDate != null) {
        whereClauses.add('CreatedDate >= :accountCreatedDate');
    }
    
    if(!whereClauses.isEmpty()) {
        query += ' WHERE ' + String.join(whereClauses, ' AND ');
    }

    System.debug('---> query: ' + query);
    System.debug('--->' + Database.query(query));
}
https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm