Building a Dynamic SOQL Query Builder in Apex

In Salesforce development, writing SOQL queries is a frequent task. However, constructing these queries dynamically based on various input parameters can significantly enhance code flexibility and reusability. In this blog post, we’ll explore how to create a dynamic SOQL query builder in Apex.

Why Use a Dynamic SOQL Query Builder?

A dynamic SOQL query builder allows developers to construct queries on the fly based on different conditions and parameters. This approach reduces the need to write custom queries repeatedly, making the codebase cleaner and more maintainable.

Implementing the Dynamic SOQL Query Builder

Let's dive into the implementation of a dynamic SOQL query builder in Apex.

Step 1: Define the Builder Class

First, we define the DynamicSOQLBuilder class with properties to hold the base query, fields, conditions, and other parameters.

public class DynamicSOQLBuilder {

    private String baseQuery;        // Base query string
    private List<String> fields;     // List of fields to select
    private List<String> conditions; // List of conditions for WHERE clause
    private String orderByField;     // Field to order by
    private String orderByDirection; // Direction of ordering (ASC/DESC)
    private Integer limitValue;      // Limit on the number of records
    private Integer offsetValue;     // Offset for pagination
    private List<String> subQueries; // List of subqueries

    // Constructor initializes the builder with the SObject type
    public DynamicSOQLBuilder(String sObjectType) {
        this.baseQuery = 'SELECT ';  // Initialize base query
        this.fields = new List<String>();
        this.conditions = new List<String>();
        this.orderByField = null;
        this.orderByDirection = 'ASC';
        this.limitValue = null;
        this.offsetValue = null;
        this.subQueries = new List<String>();
        this.baseQuery += ' FROM ' + sObjectType; // Append SObject type to base query
    }

    // Method to add fields to the SELECT clause
    public DynamicSOQLBuilder select(List<String> fields) {
        this.fields.addAll(fields);
        return this;
    }

    // Method to add conditions to the WHERE clause
    public DynamicSOQLBuilder where(String condition) {
        this.conditions.add(condition);
        return this;
    }

    // Method to specify ORDER BY field and direction
    public DynamicSOQLBuilder orderBy(String field, String direction) {
        this.orderByField = field;
        this.orderByDirection = direction;
        return this;
    }

    // Method to specify LIMIT on the number of records
    public DynamicSOQLBuilder limit(Integer limitValue) {
        this.limitValue = limitValue;
        return this;
    }

    // Method to specify OFFSET for pagination
    public DynamicSOQLBuilder offset(Integer offsetValue) {
        this.offsetValue = offsetValue;
        return this;
    }

    // Method to add subqueries
    public DynamicSOQLBuilder addSubQuery(String subQuery) {
        this.subQueries.add(subQuery);
        return this;
    }

    // Method to construct the final SOQL query
    public String build() {
        String query = baseQuery;

        // Add fields to the query
        if (!fields.isEmpty()) {
            query = 'SELECT ' + String.join(fields, ', ') + baseQuery;
        }

        // Add subqueries
        if (!subQueries.isEmpty()) {
            query += ', ' + String.join(subQueries, ', ');
        }

        // Add conditions to the query
        if (!conditions.isEmpty()) {
            query += ' WHERE ' + String.join(conditions, ' AND ');
        }

        // Add ORDER BY clause
        if (orderByField != null) {
            query += ' ORDER BY ' + orderByField + ' ' + orderByDirection;
        }

        // Add LIMIT clause
        if (limitValue != null) {
            query += ' LIMIT ' + limitValue;
        }

        // Add OFFSET clause
        if (offsetValue != null) {
            query += ' OFFSET ' + offsetValue;
        }

        return query;
    }

    // Method to execute the constructed query and return results
    public List<SObject> execute() {
        String query = build();          // Build the query
        return Database.query(query);    // Execute the query and return results
    }
}

Step 2: Example Usage

Here are some examples of how to use the DynamicSOQLBuilder to construct and execute dynamic SOQL queries.

Example 1: Basic Query

// Create a new builder for the Account object
DynamicSOQLBuilder builder = new DynamicSOQLBuilder('Account')
    .select(new List<String>{'Id', 'Name'})  // Select Id and Name fields
    .where('Name LIKE \'Test%\'')            // Add a condition
    .orderBy('CreatedDate', 'DESC')          // Order by CreatedDate descending
    .limit(10)                               // Limit to 10 records
    .offset(5);                              // Offset for pagination

String query = builder.build();              // Build the query
System.debug('Generated Query: ' + query);   // Debug the generated query

List<SObject> accounts = builder.execute();  // Execute the query
System.debug('Result: ' + accounts);         // Debug the result

Example 2: Complex Query with Multiple Conditions and Subqueries

// Create a new builder for the Contact object
DynamicSOQLBuilder complexBuilder = new DynamicSOQLBuilder('Contact')
    .select(new List<String>{'Id', 'FirstName', 'LastName', 'Email'}) // Select fields
    .where('LastName = \'Smith\'')                                     // Add first condition
    .where('Email LIKE \'%@example.com%\'')                            // Add second condition
    .orderBy('LastName', 'ASC')                                        // Order by LastName ascending
    .limit(5)                                                          // Limit to 5 records
    .offset(2)                                                         // Offset for pagination
    .addSubQuery('(SELECT Id, Name FROM Opportunities WHERE StageName = \'Closed Won\')'); // Add subquery

String complexQuery = complexBuilder.build();                         // Build the query
System.debug('Generated Complex Query: ' + complexQuery);             // Debug the generated query

List<SObject> contacts = complexBuilder.execute();                    // Execute the query
System.debug('Result: ' + contacts);                                  // Debug the result

How It Works

1. Constructor: The constructor initializes the builder with the base query for a given SObject type.

2. select(): Adds the fields to be selected in the query.

3. where(): Adds conditions to the WHERE clause.

4. orderBy(): Specifies the field and direction for sorting the results.

5. limit(): Adds a limit to the number of records returned.

6. offset(): Adds an offset to the results for pagination.

7. addSubQuery(): Adds subqueries to the main query.

8. build(): Constructs the final SOQL query string.

9. execute(): Executes the constructed query and returns the result.

This dynamic SOQL query builder allows for flexible and reusable query construction, reducing the need to write custom queries repeatedly. By using this approach, Salesforce developers can maintain cleaner codebases and adapt more quickly to changing requirements.

Conclusion

A dynamic SOQL query builder is a powerful tool in Salesforce development, enabling the construction of flexible and reusable queries. By following the implementation steps and examples provided, you can enhance your Salesforce applications' efficiency and maintainability. Happy coding!