Dynamic SOQL and SOSL Queries

Dynamic SOQL and SOSL queries are powerful tools in Apex that allow developers to build query strings at runtime based on various conditions. This flexibility is particularly useful in complex scenarios where the query parameters are not known until runtime, or when they depend on user input, configuration settings, or other runtime variables. Let’s delve deeper into this with more examples and situations where dynamic queries might be beneficial.

Dynamic SOQL and SOSL Queries Dynamic queries allow you to construct SOQL or SOSL strings at runtime, offering flexibility for complex, conditional querying scenarios

String soql = 'SELECT Name FROM Account WHERE ';
if(condition1){
    soql += 'BillingCountry = \'USA\'';
} else {
    soql += 'BillingCountry = \'EMEA\'';
}
List<Account> accounts = Database.query(soql);

Example Situations for Dynamic Queries

1. User-Defined Filters: In a scenario where users can define filters for records they wish to see (e.g., on a custom UI page), the filters selected by the user determine the WHERE clause of the query.
2. Configurable Data Sync: In a system where Salesforce synchronizes data with an external system, and the records to be synchronized are defined by configurable criteria stored in custom metadata or custom settings.
3. Multi-Object Searches: When building a global search functionality that allows searching across multiple objects based on user input, dynamically constructing the query can be particularly useful.

Dynamic Filtering Based on User Selection

In this example, performQuery can be used to query any object with filters provided at runtime. This is useful for building generic components or services in your application that work across different objects and conditions.

public with sharing class DynamicQueryController {

    public static List<sObject> performQuery(String objectName, Map<String, String> filters) {
        String soql = 'SELECT Id, Name FROM ' + objectName + ' WHERE ';
        for (String fieldName : filters.keySet()) {
            soql += fieldName + ' = \'' + String.escapeSingleQuotes(filters.get(fieldName)) + '\' AND ';
        }
        soql = soql.substring(0, soql.length() - 5); // Remove the last AND
        return Database.query(soql);
    }
}

Dynamic SOQL for Related Record Queries

This example dynamically adds a condition to the query based on whether the contactLastName parameter is provided. This technique is useful for adding optional filters to queries.

public static List<Contact> getContactsForAccount(String accountId, String contactLastName) {
    String soql = 'SELECT Id, FirstName, LastName FROM Contact WHERE AccountId = :accountId';
    if (String.isNotBlank(contactLastName)) {
        soql += ' AND LastName LIKE \'%' + String.escapeSingleQuotes(contactLastName) + '%\'';
    }
    return Database.query(soql);
}

Dynamic Field Selection

The fields to be retrieved are dynamic, based on the fields list provided at runtime. This approach is beneficial when the specific data requirements vary, such as creating a flexible reporting tool where users select the fields they want to see.

public static List<sObject> queryWithDynamicFields(String objectName, List<String> fields) {
    String soql = 'SELECT ' + String.join(fields, ', ') + ' FROM ' + objectName;
    return Database.query(soql);
}

Things To Consider Before Implementing Dynamic Queries

• Security: Always use String.escapeSingleQuotes to sanitize user inputs to avoid SOQL injection vulnerabilities.
• Readability and Maintainability: Overuse of dynamic SOQL can make your code hard to read and maintain. Where possible, prefer static queries for simplicity.
• Governor Limits: Remember that dynamic queries count towards the SOQL query limits in Apex, so monitor and optimize usage as necessary.