SOQL Criteria Builder

DSL support for building SOQL WHERE Clause in an elegant way.
Inspired by fflib_QueryFactory.
Addresses necessity to build criteria as String for fflib_QueryFactory#setCondition() method.

Benefits

• Supports all Comparison Operators
• Compile Time validation of the Field Names used in SOQL Criteria
• Dynamic SOQL Criteria based on User input
• Bind Variables
• Date Functions support
• Runtime Exception if criteria size breaks the 4000 characters limit

Examples

Simple Criteria

String simpleCriteria = 'AccountId != NULL AND (NOT LastName LIKE \'A%\') AND Department = \'Finance\'';

tmo_soqlCriteriaBuilder cb = cb()
    .isNotNull(Contact.AccountId)
    .isNotLike(Contact.LastName, 'A%')
    .equalsTo(Contact.Department, 'Finance');

System.assertEquals(simpleCriteria, cb.toCriteria());

Between Criteria

String betweenCriteria = 'NumberOfEmployees > 1000 AND NumberOfEmployees < 2000';

tmo_soqlCriteriaBuilder cb = cb()
    .greaterThan(Account.NumberOfEmployees, 1000)
    .lessThan(Account.NumberOfEmployees, 2000);

System.assertEquals(betweenCriteria, cb.toCriteria());

Dynamic Criteria

String dynamicCriteria = 'Title != NULL AND AccountId != NULL AND (Account.NumberOfEmployees > 9)';

Boolean userRequestedBigCompaniesOnly = true;
tmo_soqlCriteriaBuilder cb = cb().isNotNull(Contact.Title);
if(userRequestedBigCompaniesOnly) {
    cb
        .isNotNull(Contact.AccountId)
        .composite(
            cb()
                .configureForReferenceField(Contact.AccountId)
                .greaterThan(Account.NumberOfEmployees, 9)
        );
}

System.assertEquals(dynamicCriteria, cb.toCriteria());

Complex Criteria

String complexCriteria = 'IsWon = true AND Amount > 100000 AND'
    + ' ((Account.NumberOfEmployees > 1000 AND Account.BillingCountry = \'USA\') OR (Owner.Title = \'CEO\' AND Owner.Country = \'USA\'))';

tmo_soqlCriteriaBuilder bigUsCompanies = cb()
    .configureForReferenceField(Opportunity.AccountId)
    .greaterThan(Account.NumberOfEmployees, 1000)
    .equalsTo(Account.BillingCountry, 'USA');

tmo_soqlCriteriaBuilder ownerInUs = cb()
    .configureForReferenceField(Opportunity.OwnerId)
    .equalsTo(User.Title, 'CEO')
    .equalsTo(User.Country, 'USA');

tmo_soqlCriteriaBuilder cb = cb()
    .equalsTo(Opportunity.IsWon, true)
    .greaterThan(Opportunity.Amount, 100000)
    .composite(
        cb()
            .composite(bigUsCompanies)
            .withOr()
            .composite(ownerInUs)
    );

System.assertEquals(complexCriteria, cb.toCriteria());

Criteria with Date Functions

String dateFunctionCriteria = 'CALENDAR_MONTH(Birthdate) > 1';

tmo_soqlCriteriaBuilder cb = cb().greaterThan(CALENDAR_MONTH(Contact.Birthdate), 1');

System.assertEquals(dateFunctionCriteria, cb.toCriteria());

Examples were built under assumption of the following factory methods presence:

private static tmo_soqlCriteriaBuilder cb() {
    return tmo_soqlCriteriaBuilder.builder();
}

private static tmo_soqlCriteriaBuilder.tmo_soqlDateFunction CALENDAR_MONTH(Schema.SObjectField field) {
    return tmo_soqlCriteriaBuilder.dateFunction(tmo_soqlCriteriaBuilder.DateFunction.CALENDAR_MONTH, field);
}

Additional Thoughts

Prefer queries with :bindVariables instead of explicit values.
Underlying databases (e.g. Oracle) will create additional plan for every similar query built dynamically. This will decrease performance of the database and will require additional resources to optimize it in the background.

assertEquals('Name = :companyName', cb().equalsTo(Account.Name, ':companyName'));
assertEquals('Name IN :inValues', cb().isIn(Account.Name, ':inValues'));