Dynamic Queries in SOQL (Salesforce Object Query Language)

When we query the Salesforce database to retrieve records using the SOQL query editor, we need to create a SOQL query and provide the fields and values directly within the query.

So, we need to edit the SOQL query whenever we want to change the value. To avoid this, we can create dynamic queries in SOQL, allowing us to change values at runtime.

In this Salesforce tutorial, we will learn about dynamic queries in SOQL (Salesforce Object Query Language). In that, I will explain the functionality of dynamic queries and how to use them in SOQL queries, providing different examples.

Table of Contents

What are Dynamic Queries in SOQL?

The dynamic SOQL (Salesforce Object Query Language) queries in Salesforce enable us to create SOQL statements as strings at runtime, rather than writing them as static queries in the code.

This provides flexibility for situations where the query criteria, fields, or object types need to be determined dynamically, such as based on user input, configuration settings, or application state.

When to Use Dynamic Queries in SOQL?

Dynamic SOQL is useful when query details cannot be fully determined at compile time so that it enables queries to be created as strings at runtime. These queries are written as strings and executed using the Database.query() method.

Dynamic SOQL offers flexibility, enabling us to dynamically build queries by incorporating variables and conditional logic. For example, we can modify the WHERE clause or SELECT fields based on user input or application context.

Syntax: Declaring a Dynamic SOQL Query

String query = 'SELECT Fields FROM Object_Name WHERE Condition';
List<SObject> results = Database.query(query);

First, we need to declare a string variable to store the SOQL query. Then, create a list collection variable to store the records retrieved from the query.

To fetch records from the object, we need to pass a string variable to the Database.query() method. In this way, we can use dynamic SOQL queries in Apex.

Use Dynamic SOQL Query in Apex

In an Apex code, when we want to retrieve records from a particular account, we create an SOQL query.

Still, sometimes we need to use that query repeatedly, so instead of doing so, we can create a query, store it in a String variable, and use that variable in the Database.query() method.

In the Apex code below, we declare a string variable and store the SOQL query, which retrieves records from a custom employee object.

Then, we declare a list collection in which we will store the records retrieved from the object. To restore the records in the list, we need to use the Database.query() method and pass the string variable that contains the SOQL query we created.

After that, in the for each loop, we created an object instance and passed a list collection. We stored the retrieved records. The for loop iterates over the list and displays the documents stored in it.

First, we stored the SOQL query in a string variable and then passed that string to the Database.query() method. In this way, we can use dynamic SOQL queries in Apex.

public class SOQL {
    
      public void DynamicSOQL () {
      
        String query = ' SELECT Id, Name, domain__c FROM Employees__c ';
        List<Employees__c> empList = Database.query (query);
        
        for ( Employees__c emp : empList ) {
            System.debug ( 'Employee ID   : ' +  emp.Id + '  '+
                                      'Employee Name : ' + emp.Name + '  '+
                                      'Domain : ' + emp.domain__c );
        }    
    }
}

After executing the Apex code, you can see the records retrieved using dynamic SOQL queries in Apex.

Dynamic Queries in SOQL (Salesforce Object Query Language)

Use Dynamic SOQL Query With User Input in Apex

Now, let’s understand how to create an SOQL query so that when the user changes the value of fields based on which they want to retrieve records, we need to use a dynamic SOQL query with user input in Apex.

For example, we want to retrieve account records whose name field contains “United,” and when we change the account name, we should get records by the name we changed.

In the Apex code, we first declare a string variable to store the word that the user will provide. In the following string variable, we store the SOQL query, which will fetch the record from the Account object, and in the query, we provide a string variable instead of a static value.

In this query, we have used wildcard characters with LIKE clause. Here is the % sign. We have used both sides of the search keyword so that, regardless of the character the user provides, the % sign searches in the account name and displays those accounts.

Then we declared the list collection with the account as SObject and then passed the string variable in, which we stored the SOQL query in the Database.query() method. After that, in the for each loop, display the records using the debug method.

String searchKeyword = 'United' ;
String query = 'SELECT Name, Industry FROM Account 
                              WHERE Name LIKE \'%' + searchKeyword + '%\' ' ;
         
 List<Account> acc = Database.query (query);
          
   for ( Account a : acc ) {
              System.debug ('Account Name : ' + a.Name + '  '+
                                       ' Industry           : ' + a.Industry);
          }

As we execute the program, you can see that the result displays the fields we provided in the for loop, and only those accounts that contain the word ‘united’ are included.

Use Dynamic SOQL Query With User Input in Apex

In this way, we can use a dynamic SOQL query to change the values without modifying the query in the Apex code.

Use Dynamic Fields in SOQL Queries

In the above examples, we have seen how to use a dynamic query and how to use a dynamic query with user input.

Now, we will see how to use dynamic fields in SOQL queries so that whenever the user provides fields, the query will take those values and retrieve records accordingly.

If we use static fields, then every time we need to change them according to the requirement, we have to modify the query.

Therefore, we can use a dynamic field, allowing the user to provide fields without modifying the SOQL query. The record will be retrieved according to the user-defined fields.

List<String> fields = new List<String> { 'Name', 'Phone', 'Industry' };
            String query = 'SELECT ' + String.join ( fields, ',' ) + ' FROM Account' ;
            List<Account> acc = Database.query (query);
        
        for (Account a : acc) {
             if ( a.Industry != Null ) {
                 System.debug ( 'Account Name : ' + a.Name + '  '+
                                           'Industry : ' + a.Industry); 
              }
 }

In the above Apex code, we first created a list collection variable named fields, in which we stored the field API Names from the account object. Then, we declared a string variable to store the query.

In this query in the SELECT clause we used String.join() method, which is used to concatenate the elements of a collection, such as a list or set collection, into a single string.

Here, using this method, we added a field list to the query, which makes a dynamic field query in SOQL.

Then, we declared the list collection with the account as an SObject and passed the string variable, which stored the SOQL query, into the Database.query() method.

After that, for each loop, display only the records that contain industry field values using the debug method.

In this way, we can utilize dynamic fields in SOQL queries to modify fields at runtime in SOQL.

Use Dynamic SOQL on Values and Query

To understand this, let’s take an example. We want to fetch records from a custom object named Employee.

First, we want to gather information about employees working in Salesforce technology, and then we want to display information about those working in SharePoint technology.

Now, here, instead of retrieving employees’ records for different technologies, we retrieve them only once. Then, using conditions, we can display the records as per our requirements. For that, we need to create an apex class.

public class SOQL {
    
    public void DynamicSOQL() {
        
        String val1 = 'Salesforce';
        String val2 = 'SharePoint';
        String query = 'SELECT Id, Name, domain__c FROM Employees__c' ;
        
        List<Employees__c> EmpList = Database.query (query);
        
        for (Employees__c emp : EmpList) {
            if (emp.domain__c == val1) {
                System.debug ('Employee Name : ' + emp.Name + '     '+
                                       'Domain : ' + emp.domain__c); }
        }
        
        for (Employees__c emp : EmpList) {
            if (emp.domain__c == val2) {
                System.debug ('Employee Name : ' + emp.Name + '     '+
                                        'Domain : ' + emp.domain__c); }
        }              
    }
}

In the above Apex code, we declare two strings that hold the values the user inputs. Here, we provided Salesforce and SharePoint values, then another string for storing the SOQL query, and passed that string to the Database.query() method, which retrieves the record and is stored in the list collection.

Then, to display these domain records separately, I used two for loops, and using if conditions and string variables, you can see we displayed employee records.

Conclusion

I hope you have got an idea about dynamic queries in SOQL (Salesforce Object Query Language). In that, I have explained the functionality of dynamic queries and how to use them in SOQL queries, providing different examples.