Apex-query-builder

Apex class which allows you to perform dynamic queries more elegant, then concatenating string into a SOQL request.

Use-cases

There are situations, when you want to retrieve data from the Salesforce database, but you don't know what fields or what conditions you will need at the compilation time and you will know at the runtime. To handle this situations Salesforce provides a way to create dynamic SOQL from String, but generally this type of queries are quite complicated and String query building becomes an awkward process of building long, hardcoded query.

Selecting sObject

    new QueryBuilder(Account.class);
    new QueryBuilder('Account');
    new QueryBuilder(Account.getSobjectType());
    new QueryBuilder(new Account());
    
    new QueryBuilder().addFrom(Account.class);
    new QueryBuilder().addFrom('Account');
    new QueryBuilder().addFrom(Account.getSobjectType());
    new QueryBuilder().addFrom(new Account());

Adding fields

    new QueryBuilder(Account.class).addField(Account.Name);
    new QueryBuilder(Account.class).addField('Name');
    new QueryBuilder(Account.class).addField('Name, Id');
    new QueryBuilder(Account.class).addFields('Name, Id');
    new QueryBuilder(Account.class).addFields(new List<String>{'Name'});
    new QueryBuilder(Account.class).addFields(new Set<String>{'Name'});

Adding MORE fields

All fields

    new QueryBuilder(Account.class).addFieldsAll();
    new QueryBuilder(Account.class).addFieldsAllCreatable();
    new QueryBuilder(Account.class).addFieldsAllUpdatable();

Field Sets

    new QueryBuilder(Account.class).addFieldSet('Field_Set_Name');
    
    FieldSet retrievedFieldSet = ...//get fieldSet
    new QueryBuilder(Account.class).addFieldSet(retrievedFieldSet);

Sub queries

    new QueryBuilder(Account.class)
        .addSubQuery(new QueryBuilder('Contacts').addField(Contact.Name));

Additional methods

    new QueryBuilder(Account.class)
        .setLimit(1)
        .setOffset(1)
        .addOrderAsc('Id')
        .addOrderDesc(Account.Name)
        .addGroupBy('Id');

CRUD and FLS built-in check

    new QueryBuilder(Account.class).setCheckCRUDAndFLS(true);
    new QueryBuilder(Account.class).setCheckCRUD(false);
    new QueryBuilder(Account.class).setCheckFLS(); //true

Results

    //SELECT Id FROM Account
    new QueryBuilder(Account.class).toString();
    
    //SELECT count() FROM Account
    new QueryBuilder(Account.class).toStringCount();
    
    //1
    new QueryBuilder(Account.class).toCount();
    
    //[Account:(...)]
    new QueryBuilder(Account.class).toList();
    
    //[account_id=Account:(...)]
    new QueryBuilder(Account.class).toMap();
    
    //Account:(...)
    new QueryBuilder(Account.class).toSObject();
    
    //[account_id]
    new QueryBuilder(Account.class).toIdSet();
    
    //[account_id]
    new QueryBuilder(Account.class).extractIds('Id');
    
    //[account_name]
    new QueryBuilder(Account.class).extractField('Name');

Conditions

Simple Condition

    new QueryBuilder(Account.class)
        .addConditions()
        .add(new QueryBuilder.SimpleCondition('Name = \'Test\''))
        .endConditions();

Null Condition

    new QueryBuilder(Account.class)
        .addConditionsWithOrder('1 AND 2')
        .add(new QueryBuilder.NullCondition('Name').isNull())
        .add(new QueryBuilder.NullCondition(Account.Id).notNull())
        .endConditions();

Compare Condition

    new QueryBuilder(Account.class)
        .addConditionsWithOrder('1 OR 2 OR 3')
        .add(new QueryBuilder.CompareCondition('Name').eq('Test'))
        .add(new QueryBuilder.CompareCondition('Name').ne('Not Test'))
        .add(new QueryBuilder.CompareCondition('NumberOfEmployees').gt(0))
        .endConditions();

Like Condition

    new QueryBuilder(Account.class)
        .addConditionsWithOrder('1')
        //Name LIKE '%st%'
        .add(new QueryBuilder.LikeCondition('Name').likeAnyBoth('st'))
        .endConditions();

In Condition

    new QueryBuilder(Account.class)
        .addConditionsWithOrder('1 AND 2')
        .add(new QueryBuilder.InCondition('Name').inCollection(new List<String>{'Test'}))
        .add(new QueryBuilder.InCondition('Name').notIn(new Set<String>{'Not Test'}))
        .endConditions();

Record Type Condition

    new QueryBuilder(Account.class)
        .addConditions()
        .add(new QueryBuilder.RecordTypeCondition('record_type_name'))
        .endConditions();

Example of a complex query builder

List<Account> accounts = (List<Account>) new QueryBuilder(Account.class)

        //fields
        .addField(Account.Name)
        .addField('ParentId')
        .addFields('Id, NumberOfEmployees')
        .addFields(new List<String>{'Name'})
        .addFields(new Set<String>{'Name'})
        .addFieldSet('name_of_the_field_set')
        .addFieldsAll()

        //subquery
        .addSubQuery(new QueryBuilder('Contacts')
            .addFieldsAll(Contact.class))

        //conditions
        .addConditions()
        .add(new QueryBuilder.SimpleCondition('Name = \'Account-1\''))
        .add(new QueryBuilder.NullCondition(Account.Name).notNull())
        .add(new QueryBuilder.CompareCondition(Account.Name).eq('Account-1'))
        .add(new QueryBuilder.LikeCondition(Account.Name).likeAnyRight('Account'))
        .add(new QueryBuilder.InCondition(Account.Name).inCollection(new Set<String>{'Account-1'}))
        .add(new QueryBuilder.RecordTypeCondition('name_of_the_record_type'))

        //order of execution of conditions
        .setConditionOrder('1 AND 2 OR ((3 AND 4) OR 5) AND 6')
        .endConditions()
        .setLimit(10)
        .setOffset(1)
        .addOrderAsc('Id')
        .preview()
        .toList();

Example of mocking query builder in test

@IsTest
public static void testStub1() {
        final String EXPECTED_SOQL = 'SELECT Name FROM Account';

        QueryBuilder stubbedQueryBuilder = new QueryBuilder(Account.class)
                .buildStub()
                .addStubToString(EXPECTED_SOQL)
                .addStubToList(new List<Account>{new Account(Name='Stubbed-Account')})
                .applyStub();

        System.assertEquals(EXPECTED_SOQL, stubbedQueryBuilder.toString());
        System.assertEquals(1, stubbedQueryBuilder.toList().size());
        System.assertEquals('Stubbed-Account', stubbedQueryBuilder.toList()[0].Name);
}

ToDo

Increase test coverage
Refactor some functions/classes names
Make IN conditions work with list references
Improve Documentation

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

4an70m / apex-query-builder

Programming Languages

Labels

Projects that are alternatives of or similar to apex-query-builder