All About Salesforce Dynamic SOQL

In this blog, we will discuss all about Salesforce Dynamic SOQL. In Salesforce development, querying data from the database is a common task, and to do so we use Salesforce Object Query Language (SOQL) to execute. It is a way to retrieve the records from the database.

In Salesforce we have two types of SOQL: static and dynamic. Static is a hard-coded, dynamic offers the flexibility to construct queries at runtime.

What is Dynamic SOQL?

Dynamic SOQL lets us build the query string on the fly. It provides the flexibility to construct queries at runtime. It gives developers powerful control over query generation based on variables or user inputs.

Unlike Static SOQL, where the query structure remains fixed, Dynamic SOQL allows you to modify the query based on the conditions, filters, or variables at the time of execution. Making it highly useful when the fields, conditions, or filters are not known until the code executes.

It provides greater flexibility in dealing with varying query scenarios by using string concatenation in Apex and putting variables or inputs into the query.

For example, you can create a search based on input from an end user or update records with varying field names.

How to use Dynamic SOQL?

To execute dynamic query we have to use these methods: Database.query(queryString),  Database.query(queryString, accessLevel) or Database.queryWithBinds(queryString, bindMap, accessLevel). These methods are the part of Salesforce’s Database class and provide different levels of control over dynamic queries, making them useful in a wide range of scenarios based on the complexity of our querying needs.

Let’s understand each method with an example.

Database.query(queryString)

It is a database method which accepts a string as input. It can return the single sObject if the query returns the single record or can be a list if the query returns a list of records.

String query = 'SELECT Id FROM Account';
List<Account> accounts = Database.query(query);
System.debug('Accounts '+accounts);
System.debug('Accounts list size '+accounts.size());

Above we have written a simple query in a developer console’s anonymous window to extract the account data from the database. Here, we have defined our query in a string variable named “query” and then we have passed it to the Database.query method and hence it will return the list of Accounts.

String query = 'SELECT Id FROM Account LIMIT 1';
Account account = Database.query(query);
System.debug('Accounts '+account);

The above code is used when we want to return only a single record, So here we haven’t defined a List of Accounts instead we have declared a single sObject of type Account. Likewise, we can use Database.query methods to execute our query based on our output.

Database.query(queryString, accessLevel)

It is a database method which accepts two parameters one is of type string( queryString) and the other is of type System.AccessLevel (accessLevel) as an input. It can return the single sObject if the query return the single record or can be a list if the query returns a list of records.

1. queryString – It defines the query which we want to execute to retrieve our data from the database.
2. accessLevel – It specifies the execution mode which is either SYSTEM_MODE or USER_MODE.

This variation of the database.query method allows us to specify how Salesforce sharing rules should be applied to the query results (e.g., with or without sharing rules).

String query = 'SELECT Id FROM Account';
List<Account> accounts = Database.query(query,AccessLevel.USER_MODE);
System.debug('Accounts with user mode '+accounts);
System.debug('Accounts list size with user mode '+accounts.size());

Above is an example for the variation we have in the Database.query with execution mode. Currently I have logged in as System Admin so we will be getting all the records here.

Database.query with bind variables

In this variation, we can pass bind variables which will execute exactly like our where condition in the SOQL. Bind variables are basically the variables that we use in our SOQL query which is a kind of placeholder for a specific value to be provided later. We use them for our where conditions.

Let’s check how we can use bindVariable with Database.query method. Here we have to specify it as a string variable to use it in our query. Bind variables in the query must be within the scope of the database operation.

String acctName = 'GenePoint';
String query = 'SELECT Id,Name FROM Account WHERE Name = :acctName';
List<Account> accounts = Database.query(query);
System.debug('accounts '+accounts);

Below is not allowed in while using bind variable in the database.query method and will throw the Query Exception. However, it is allowed in our inline SOQL. Instead, if we assign acc.Name in a String and then add in our Database.query method, It will execute successfully.

Account acc = new Account(Name = 'GenePoint');
String query = 'SELECT Id, Name FROM Account WHERE Name =: acc.Name';
List<Account> accounts = Database.query(query);

Database.queryWithBinds(queryString, bindMap, accessLevel)

It is a database method which accepts three parameters one is of type string( queryString), the other is of type Map<String, Object> (bindMap) and the other is of type System.AccessLevel (accessLevel) as an input. It can return the single sObject if the query returns the single record or can be a list if the query returns a list of records.

1. queryString – It defines the query which we want to execute to retrieve our data from the database.
2. bindMap – It defines the map containing keys for each bind variable specified in the SOQL queryString along with its corresponding value. Also, keys must be unique and non-null.
3. accessLevel – It specifies the execution mode which is either SYSTEM_MODE or USER_MODE.

Earlier we checked how we can use bind variables in our Database.Query method. So another option which is available to use in API version 57 and later is Database.QueryWithBinds method. Here instead of using the apex variable, we can specify our bind variable in the map and directly refer to them in our query string.

Example 1

String query = 'SELECT Id,Name FROM Account WHERE Name =:accountName';
Map<String, Object> nameBind = new Map<String, Object>{'accountName' => 'GenePoint'};
List<Account> accounts = Database.queryWithBinds(query,nameBind, AccessLevel.User_mode);
System.debug('Accounts '+accounts);
System.debug('Accounts list size '+accounts.size());

Example 2

Account acc = new Account(Name = 'GenePoint');
Map<String, Object> nameBind = new Map<String, Object>{'accountName' => acc.Name};
String query = 'SELECT Id,Name FROM Account WHERE Name =:accountName';
List<Account> accounts = Database.queryWithBinds(query,nameBind, AccessLevel.User_mode);
System.debug('Accounts '+accounts);
System.debug('Accounts list size '+accounts.size());

This variation is the most advanced version of the Salesforce Dynamic SOQL Method. It allows you to pass additional binding variables through a bindMap, and specify whether to execute in the system mode or user mode. This is useful when you want to use dynamic SOQL along with variables that need to be passed at runtime.

When to use Dynamic SOQL?

Salesforce Dynamic SOQL becomes invaluable when you need to create queries that are highly customisable and flexible. Here are some scenarios where it is beneficial:

1. Dynamic Fields: When the fields to be queried are not known beforehand and need to be selected at runtime.
2. Custom Filtering: If filters or conditions are based on user input or other runtime data, dynamic SOQL enables us to construct the query accordingly.
3. Multiple Query Variants: Instead of writing multiple static queries to cover all possibilities, dynamic SOQL lets you handle various scenarios with a single flexible method.

Things to remember while working with Salesforce Dynamic SOQL

While dynamic SOQL offers flexibility, it’s important to use it carefully. Here are some best practices to follow:

1. Prevent SOQL Injection: Always sanitise inputs by using String.escapeSingleQuotes() when building queries based on user input. It basically helps us to remove any single quote inside our query which helps us to build a secured query and prevent SOQL injection attacks, which can compromise the security of your data.
2. Limit the Number of Fields Queried: Only query the fields that are absolutely necessary. Dynamic SOQL can be optimised by keeping the query simple and focused.
3. Bulkify Code: Ensure your dynamic SOQL is bulk-safe to handle large data sets efficiently. Avoid querying in loops to stay within Salesforce governor limits.
4. Use Binding Variables: Where possible, use binding variables to filter out our query which helps us to retrieve data which is needed and increase performance.
5. Test for Different Scenarios: Since dynamic SOQL is built at runtime, you must test it thoroughly with different inputs to make sure it behaves as expected in all possible scenarios.

Related methods that we can use in the Salesforce Dynamic SOQL

Database.countQuery

This method returns the record count of the dynamic SQL query when executed. It also accepts the required string parameter which defines our dynamic SOQL query and another optional parameter is for the accessLevel which defines whether to execute in the user mode or system mode.

String query = 'SELECT count() FROM Account';
Integer recordCount = Database.countQuery(query);
System.debug('Accounts size '+recordCount);

Database.countQueryWithBinds

It works similarly to Database.queryWithBinds and accepts three parameters – String (query), Map<String,Object> (bindMap), System.AccessLevel (accessLevel). It returns the count of records that dynamic SOQL will return when executed.

String query = 'SELECT count() FROM Account WHERE Name =:accountName';
Map<String, Object> nameBind = new Map<String, Object>{'accountName' => 'GenePoint'};
Integer recordCount = Database.countQueryWithBinds(query,nameBind, AccessLevel.User_mode);
System.debug('record count:- '+recordCount);

Database.getQueryLocator & Database.getQueryLocatorWithBinds

These methods are used in the Batch class or Visualforce. Return the list of records that are being executed in the dynamic SOQL.

Let’s understand it with an example

Scenario – Here we are assuming that in UI we have some component where the user is selecting or providing the input for  object name, field name. We will be checking how Apex will be customized according to this scenario.

We have created an Apex class as DynamicDataController which will include the logic for the dynamic SOQL to run the query at run time based on the user input.

/*
************************************************************************************************************************************************
Apex Class Name : DynamicDataController
Created Date : November 1, 2024
@description : This class is used for handling dynamic retrieval of data
@author : Kashish Bansal
Modification Log:
Ver Date Author Modification
1.0 01-11-2024 Kashish Bansal Initial Version
*************************************************************************************************************************************************
*/
public class DynamicDataController {

/*
**********************************************************************************************************************************************
@Method Name : getDataDynamically
@author : Kashish Bansal
@description : method is to retrieve data based on the user input
@param : objectName,fieldArray
@return : List<sObject>
**********************************************************************************************************************************************
*/

public static List<sObject> getDataDynamically(String objectName, List<String>fieldArray) {
String query = 'SELECT Id';
List<String> fieldList = new List<String>();

sObjectType type = Schema.getGlobalDescribe().get(objectName);
Map<String,Schema.sObjectField> fieldsMap = type.getDescribe().fields.getMap();
for(String apiStr : fieldsMap.keySet()) {
for(String fieldName : fieldArray) {
sObjectField fl = fieldsMap.get(apiStr);
if(fieldName == fl.getDescribe().getLabel()) {
fieldList.add(apiStr);
}}
}
for(String f: fieldList) {
query += ', '+f;
}
query += ' FROM '+objectName;
List<sObject> quriedData = Database.query(query);
System.debug('size '+quriedData.size());
return quriedData;
}
}

Above is the apex class we have created to execute the dynamic SOQL logic. In the getDataDynamically, we have passed two input parameters which will provide the details for the object name and the field information (in an array which hold the multiple field labels) we want to get displayed. The query is the string variable which will create the dynamic SOQL.

Hence the user is passing the labels of the fields which can contain the standard and custom fields also, Additionally, we have to fetch the API names to pass it in our SOQL. Then we retrieve the list by using Database.query method which will return the sObject List.

FAQ’s

1. What are the system mode and user mode mean?

System Mode – When code runs in System mode object and field-level permissions of the current user are ignored and user will be able to access all data.

User Mode – In user, mode, the current user’s object permissions, field-level security, and sharing rules are enforced.

2. What is SOQL Injection?

When user-supplied invalidated input, the risk of SOQL Injection gets higher. It majorly happens in the dynamic SOQL query. It’s always a best practice to use String.escapeSingleQuotes() which removes all the single quotes in the query which we have provided to execute dynamically.

3. What is the difference between Static SOQL and Dynamic SOQL?

Static SOQL has a fixed query structure, while Dynamic SOQL allows the query to be built dynamically at runtime based on conditions or variables.

Conclusion

Salesforce Dynamic SOQL allows us to build queries even when we don’t know the specific object, fields, or conditions we need to display while customizing our code. It enables us to create queries at runtime and makes our code dynamic, allowing for versatile use. By knowing when and how to use it—and keeping security and performance in mind—we can create more powerful and efficient solutions.

Get a complete Roadmap To Learn Salesforce Admin And Development

Name *

First

Last

Email *

Comment or Message

Submit