Apex Class

Salesforce Dynamic SOQL and SOSL Queries

Dynamic SOQL/SOSL queries provide the ability to execute SOQL as a string at runtime, while static queries are written as literals in your code

Those types of queries are useful when you need flexibility and adaptability in your queries, or when you don't know the exact query structure beforehand

Dynamic SOQL/SOSL Queries in Salesforce

Query String Building

This is one of the most flexible methods since you have a string which you will use as your query, allowing you to modify the string using all variables that you want within the code

SOQL Query

In this example, we'll use different methods to concatenate strings to create a SOQL dynamic query.

List<String> dynamicFields = new List<String>{'Id', 'Name', 'Industry'};
String dynamicQuery = 'SELECT ';

// INCLUDE FIELDS
for (Integer i = 0; i < dynamicFields.size(); i++) {
    // IF IS THE LAST ITERATION DON'T ADD AN ", "
    dynamicQuery += i + 1 == dynamicFields.size() ? dynamicFields[i] + ' ' : dynamicFields[i] + ', ';
}

// SPECIFY THE OBJECT, CONDITIONS AND SORT
dynamicQuery += 'FROM Account WHERE ';
dynamicQuery += dynamicFields[1] + ' = \'Jack\' ';
dynamicQuery += 'ORDER BY CreatedDate';

List<sObject> dynamicRecords = Database.query(dynamicQuery);

SOSL Query

Here we'll use Custom Labels to make it even more dynamic, allowing you to modify the query without making any changes to the code.

First, we'll go to Setup > Custom Label to declare the fields that we will use for this SOSL query.

Declaring a Custom Label with fields for a Dynamic SOSL Query in Salesforce

Once we have the Custom Label we can start coding, remember that you can change it wherever you want.

String dynamicFields = System.Label.DynamicSOSL;
String searchTerm = 'Jack';

String soslQuery = 'FIND {' + searchTerm + '} IN ALL FIELDS RETURNING Account(' + dynamicFields + ')';
List<List<SObject>> searchResults = Search.query(soslQuery);

Condition Variable

When you have variables that could be dynamically included in the WHERE clause, you can perform your query in a casual way without concatenating a lot of strings.

Date todayDate = Date.today();
List<Account> accList = [SELECT Id, Name FROM Account WHERE CreatedDate =: todayDate];

Set<Id> contIds = new Set<Id>{'003Dn000009zbZJIAY', '003Dn000009zbZaIAI'};
List<Contact> contList = [SELECT Id, Firstname, Lastname FROM Contact WHERE Id IN: contIds];

You can use a variable using ":" before using the variable on the query.