All You Need to Know About Dynamic SOQL Query

Dynamic SOQL (Structured Query Language) queries are a powerful tool for querying and manipulating data in Salesforce. Unlike static SOQL queries, which are written directly into Apex code, dynamic SOQL queries allow for more flexibility and customization based on user input and other variables.

In a dynamic SOQL query, the query string is constructed at runtime, rather than being hard-coded into the code. This allows for a more dynamic approach to querying data, which can be especially useful in scenarios where the specific criteria for the query may change based on user input or other factors.

For example, consider the following static SOQL query:

List<Account> accounts = [SELECT Name, Industry FROM Account WHERE Industry = 'Finance'];

This query returns a list of all Account records where the Industry field is set to 'Finance'. However, if we want to allow the user to specify the industry value dynamically, we can use a dynamic SOQL query instead:

String industry = 'Finance';
String queryString = 'SELECT Name, Industry FROM Account WHERE Industry = \'' + industry + '\'';
List<Account> accounts = Database.query(queryString);

In this example, we have defined a variable industry that represents the value we want to filter by, and we have constructed a query string that includes this value dynamically. The Database.query method is then used to execute the query and return a list of matching Account records.

Don't forget to check out: Understanding sObjects in Salesforce: A Comprehensive Guide

To create a dynamic SOQL query at runtime, use the Database.query or Database.queryWithBinds methods in one of the following ways.

• Return one sObject when the query returns one record:

sObject s = Database.query(string);

• Returns a list of sObjects when the query returns more than one record:

List<sObject> sobjList = Database.query(string);

• Return a list of sObjects using the bind variable map:

List<sObject> sobjList = Database.queryWithBinds(string, bindVariablesMap, accessLevel);

One key advantage of using dynamic SOQL queries is that they allow for more flexible and customizable user interfaces. For example, if you are building a search page that allows users to search for records based on multiple criteria, dynamic SOQL queries can be used to construct a query string that includes all of the necessary filters.

Consider the following example:

String firstName = 'John';
String lastName = 'Smith';
String city = 'San Francisco';
String queryString = 'SELECT Name, Email, Phone FROM Contact WHERE FirstName = \'' + firstName + '\'';
queryString += ' AND LastName = \'' + lastName + '\'';
queryString += ' AND MailingCity = \'' + city + '\'';
List<Contact> contacts = Database.query(queryString);

In this example, we are constructing a query string that includes filters for the first name, last name, and city fields on the Contact object. By building the query string dynamically, we can allow users to specify any combination of these filters, and the query will return only the matching Contact records.

Additional Dynamic SOQL Methods

The dynamic SOQL examples in this topic show how to use the Database.query and Database.queryWithBinds methods. These methods also use Dynamic SOQL:

• Database.countQuery and Database.countQueryWithBinds: Returns the number of records that would be returned by a dynamic SOQL query at run time.
• Database.getQueryLocator and Database.getQueryLocatorWithBinds: Create a QueryLocator object used in batch Apex or Visualforce.

Check out another amazing blog by Rahul here: Einstein Activity Capture | The Ultimate Guide