Salesforce Dynamic SOQL

Salesforce is a powerful platform that provides various tools for developers to interact with its vast database. One such tool is SOQL (Salesforce Object Query Language), which allows you to retrieve records from the Salesforce database. However, in some cases, you might need to create flexible queries that can adapt to different user inputs or conditions. This is where Dynamic SOQL comes into play.

What Is Dynamic SOQL In Apex?

Dynamic SOQL in Salesforce refers to the ability to construct SOQL queries as strings at runtime in Apex code. Unlike static SOQL, where the query is hard-coded, Dynamic SOQL provides the flexibility to build queries dynamically based on various conditions, user inputs, or logic that might not be known until the code is executed.

Example of Dynamic SOQL in Salesforce

Let’s consider a real-world scenario where dynamic SOQL is useful.

Suppose you have a Visualforce page where users can search for accounts based on their input, such as account name, industry, or rating. Instead of writing multiple static SOQL queries for each possible combination, you can use dynamic SOQL to construct the query based on the user’s input.

public with sharing class AccountSearchController {     public String searchTerm { get; set; }     public String industryFilter { get; set; }     public String ratingFilter { get; set; }     public List<Account> getAccounts() {         String queryString = ‘SELECT Id, Name, Industry, Rating FROM Account WHERE Name LIKE \’%’ + searchTerm + ‘%\”;         if (industryFilter != null && industryFilter != ”) {             queryString += ‘ AND Industry = \” + industryFilter + ‘\”;         }         if (ratingFilter != null && ratingFilter != ”) {             queryString += ‘ AND Rating = \” + ratingFilter + ‘\”;         }         return Database.query(queryString);     } }

In this example, the SOQL query string is built dynamically based on the user’s input, allowing for a flexible and powerful search experience.

How to Write a Dynamic SOQL Query in Salesforce

To write a dynamic SOQL query in Salesforce, you’ll typically use the Database.query() method in Apex. This method allows you to pass a string containing your SOQL query and returns a list of sObjects that match the query.

Here’s a basic syntax of how to write dynamic SOQL:

String queryString = ‘SELECT Id, Name FROM Account WHERE Name LIKE \’A%\”; List<Account> accounts = Database.query(queryString);

In this example, the query string is created at runtime and passed to the Database.query() method to retrieve a list of Account records where the name starts with the letter ‘A’.

Query Method of the Database Class

The Database.query() method is the primary way to execute dynamic SOQL in Apex. This method requires a single argument: the SOQL query string. Once executed, it returns a list of sObjects that match the criteria specified in the query.

Here’s how you can use it:

String queryString = ‘SELECT Id, Name FROM Contact WHERE Email = \’[email protected]\”; List<Contact> contacts = Database.query(queryString);

In this example, the query string is dynamically created to search for a contact with a specific email address.

Using Dynamic SOQL Bind Variables

Bind variables in dynamic SOQL help protect against SOQL injection attacks and improve code readability. Instead of concatenating user input directly into the query string, you can use bind variables to safely insert values.

public with sharing class ContactSearchController {     public String emailFilter { get; set; }     public List<Contact> getContacts() {         String queryString = ‘SELECT Id, Name, Email FROM Contact WHERE Email = :emailFilter’;         return Database.query(queryString);     } }

Next Topic