Apex Query

A query builder for dynamic SOQL construction.

Support: If you find this library helpful, please consider sharing it on Twitter or recommending it to your friends or colleagues.

| Environment | Installation Link | Version | | --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | | Production, Developer | <a target="_blank" href="https://login.salesforce.com/packaging/installPackage.apexp?p0=04tGC000007TPn6YAG"><img src="docs/images/deploy-button.png"></a> | ver 3.0.5 | | Sandbox | <a target="_blank" href="https://test.salesforce.com/packaging/installPackage.apexp?p0=04tGC000007TPn6YAG"><img src="docs/images/deploy-button.png"></a> | ver 3.0.5 |

---

Translations

• 简体中文

Release v3.0.0

Version 2.0 was too complex to maintain and use. Version 3.0 aims for simplicity, though there is limited room for improvement. During the redesign, I also considered whether simple string concatenation would suffice.

• Key Updates
  • Performance improved by 30%. This is a modest gain, roughly a 7 vs 10 CPU time difference.
  • Strings are now first-class citizens, and strong type checking has been removed.
  • Rarely used features have been removed.
• New Features:
  • Query Composition
  • Query Chaining
  • Query Template

---

Table of Contents

• 1. Naming Conventions
  • 1.1 Naming Readability
  • 1.2 Naming Confliction
• 2. Overview
  • 2.1 Query Class
  • 2.2 Query Composition
  • 2.3 Query Chaining
  • 2.4 Query Template
  • 2.5 Query Execution
• 3. Keywords
  • 3.1 From Statement
  • 3.2 Select Statement
  • 3.3 Where Statement
  • 3.4 Order By Statement
  • 3.5 Group By Statement
  • 3.6 Other Keywords
• 4. Filters
  • 4.1 Comparison Filter
  • 4.2 Logical Filter
• 5. Functions
  • 5.1 Aggregate Functions
  • 5.2 Date/Time Functions
  • 5.3 Other Functions
• 6. Literals
  • 6.1 Date Literals
  • 6.2 Currency Literals
• 7. License

1. Naming Conventions

1.1 Naming Readability

The following naming conventions are used to improve query readability:

| | Description | Naming Convention | Reasoning | Example | | ------------- | --------------------------------------------------- | ----------------- | ------------------------------------------------------------------------------------- | ------------------------------------------------------------------ | | Keywords | Core structures of SOQL. | camelCase | Keywords should clearly correspond to their SOQL equivalents. | selectBy, whereBy, groupBy, havingBy, orderBy | | Operators | Logical and comparison operators. | lowercase | Operators should be concise and operator-like, using abbreviations where appropriate. | eq, ne, gt, gte, lt, lte, inx, nin | | Functions | Used for aggregation, formatting, date access, etc. | camelCase | Camel case aligns with Apex method names and is easy to type. | count, max, toLabel, format, calendarMonth, fiscalYear | | Literals | Only date and currency literals. | UPPER_CASE | These are constant-like values, so static constant variable naming is preferred. | LAST_90_DAYS(), LAST_N_DAYS(30), CURRENCY('USD', 100) |

1.2 Naming Confliction

To avoid conflicts with existing keywords or operators, follow these conventions:

1. Use the <keyword>By() format for SOQL keywords, such as selectBy, whereBy, groupBy, havingBy, orderBy.
2. Use the <operator>x() format for conflicting operators only, such as orx(), andx(), inx(), likex().

2. Overview

2.1 Query Class

All operators and functions are implemented as static methods of the Query class. Referencing them with a Query. prefix each time can be tedious. When possible, extend the Query class so all static methods can be referenced directly.

public with sharing class AccountQuery extends Query {
    public List<Account> listAccount() {
        return (List<Account>) Query.of('Account')
            .selectBy('Name', toLabel('Industry'))
            .whereBy(orx()
                .add(andx()
                    .add(gt('AnnualRevenue', 1000))
                    .add(eq('BillingState', 'Beijing')))
                .add(andx()
                    .add(lt('AnnualRevenue', 1000))
                    .add(eq('BillingState', 'Shanghai')))
            )
            .orderBy(orderField('AnnualRevenue').descending().nullsLast())
            .run();
    }
}

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry)
FROM Account
WHERE ((AnnualRevenue > 1000 AND BillingState = 'Beijing')
    OR (AnnualRevenue < 1000 AND BillingState = 'Shanghai'))
ORDER BY AnnualRevenue DESC NULLS LAST

2.2 Query Composition

The main advantage of this library is its flexibility: you can split a complete query into multiple segments, freely combine or reorder them as needed, and assemble the final query conditionally. For example, the SOQL above can be broken down into several dynamic components and then composed together as required:

public with sharing class AccountQuery extends Query {
    public List<Account> runQuery(List<Object> additionalFields,
        Decimal beijingRevenue,
        Decimal shanghaiRevenue) {

        Query q = baseQuery();
        q.selectBy(additionalFields);
        /**
         *  Don't worry if `andx()` or `orx()` in the where condition
         *  have zero or only one filter; SOQL will always be built correctly.
         */
        q.whereBy(orx());
        q.whereBy().add(beijingRevenueGreaterThan(beijingRevenue));
        q.whereBy().add(shanghaiRevenueLessThan(shanghaiRevenue));
        return q.run();
    }

    public Query baseQuery() {
        Query q = Query.of('Account');
        q.selectBy('Name');
        q.selectBy(toLabel('Industry'));
        return q.orderBy(orderField('AnnualRevenue').descending().nullsLast());
    }

    public Filter beijingRevenueGreaterThan(Decimal revenue) {
        return andx()
            .add(gt('AnnualRevenue', revenue))
            .add(eq('BillingState', 'Beijing'));
    }

    public Filter shanghaiRevenueLessThan(Decimal revenue) {
        return andx()
            .add(lt('AnnualRevenue', revenue))
            .add(eq('BillingState', 'Shanghai'));
    }
}

2.3 Query Chaining

Parent and child relationships can be assembled using query chaining. Multiple levels of parent and child chaining are supported, except for queries with a group by clause.

public with sharing class AccountQuery extends Query {
    public List<Account> listAccount() {
        Query parentQuery = Query.of('Account')
            .selectBy('Name', format(convertCurrency('AnnualRevenue')));
        Query childQuery = Query.of('Contact').selectBy('Name', 'Email');

        return (List<Account>) Query.of('Account')
            .selectBy('Name', toLabel('Industry'))
            .selectParent('Parent', parentQuery)   // Parent Chaining
            .selectChild('Contacts', childQuery)   // Child Chaining
            .run();
    }
}

Equivalent to the following SOQL:

SELECT Name, toLabel(Industry),
    Parent.Name, FORMAT(convertCurrency(Parent.AnnualRevenue)) -- Parent Chaining
    (SELECT Name, Email FROM Contacts)                         -- Child Chaining
FROM Account

Without query chaining, the following code achieves the same result:

public with sharing class AccountQuery extends Query {
    public List<Account> listAccount() {
        return (List<Account>) Query.of('Account')
            .selectBy('Name', toLabel('Industry'),
                'Parent.Name', format(convertCurrency('Parent.AnnualRevenue')),
                '(SELECT Name, Email FROM Contacts)')
            .run();
    }
}

2.4 Query Template

When you want to run the same Query with different binding variables, use the following pattern. Note: Query templates should be built with var(binding variable name).

public with sharing class AccountQuery extends Query {
    public static Query accQuery {
        get {
            if (accQuery == null) {
                accQuery = Query.of('Account')
                    .selectBy('Name', toLabel('Industry'))
                    .selectChild('Contacts', Query.of('Contact')
                        .selectBy('Name', 'Email')
                        .whereBy(likex('Email', var('emailSuffix'))) // var 1
                    )
                    .whereBy(andx()
                        .add(gt('AnnualRevenu