Piotr Gajek
written byPiotr Gajek
posted on July 10, 2023
Technical Architect and Full-stack Salesforce Developer. He started his adventure with Salesforce in 2017. Clean code lover and thoughtful solutions enthusiast.

SOQL Lib

Hello there! 👋

Let's talk about the Selector Layer in Apex.
What is it? Why you need it? How to use it? And what is the BTC SOQL Lib!

Introduction

Chapter 1 - Understand what the Select Layer is.

selectors in apex

The idea behind the Selector Layer is quite simple: gather all your object queries scattered across the entire project and keep them in one place called the selector class.

By doing that you you will have a Selector class per different object types like AccountsSelector.cls, ContactsSelector.cls or OpportunitiesSelector.cls.

The Selector Layer aims to address the following issues:

  • Query inconsistencies - when the same queries are made from different places for the same information or criteria (or subtle variants), it can lead to inconsistencies in your application.
  • Query data inconsistencies - developers can end up repeating another query over the same record set simply to query the required fields. Set default query fields so you have the guarantee that System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: X.Y. will not occur.
  • Security inconsistencies - force Field Level Security and Sharing Rules for all of your queries when needed.

The Selector Layer offers several benefits:

  1. An additional level of abstraction - the selector layer provides an additional level of abstraction that allows you to control the execution of SOQL. It's a broad statement, but that level is a source of other benefits.
  2. Mocking - selector classes enable you to mock return values in unit tests.
    • Mock external objects (__x) - External objects (__x) cannot be inserted in unit tests. You need to mock the results of all SOQLs. Of course, there is if (Test.isRunningTest()), but it is not an elegant and clean solution.
    • Mock custom metadata - Custom metadata cannot be inserted in unit tests unless the developer uses the Metadata API. Mocking can be a solution.
  3. Control field-level security - the best practice is to execute SOQLs WITH USER_MODE to enforce field-level security and object permissions of the running user. The selector layer can apply WITH USER_MODE by default to all queries, so the developer does not need to worry about it. Developers can also add WITH SYSTEM_MODE` to all SOQLs from a specific selector. I believe selectors make FLS control easier.
  4. Control sharings rules - the selector allows the execution of different SOQLs within the same class in different sharing modes (with sharing/without sharing).
  5. Avoid duplicates - generic SOQLs like getById and getByRecordType can be stored in the selector class. It makes developers' lives easier when they have predefined and tested methods.
  6. Default configuration - the selector class can provide default SOQL configurations like default fields, FLS settings, and sharing rules. I was on a project where sharing rules were screwed up and each SOQL needed a default condition OwnerId = :UserInfo.getUserId(). It was quite simple to achieve with selectors by adding a default condition to all SOQLs.
  7. Cache - the selector can have additional logic to persist SOQL results. That approach can be beneficial in places where we need to save SOQL limits.

Read about the benefits in this StackExchange response.
More details about Selector Layer you can find in the Learn Selector Layer Principles (Trailhead).

FFLib Selector

Chapter 2 - FFLib Selector.

The most popular selector implementation is the FFLib Selector.

Let's see what the FFLib Selector code looks like.
Below you will find a simple implementation of OpportunitiesSelector.

public with sharing class OpportunitiesSelector extends SObjectSelector
{
    public List<Schema.SObjectField> getSObjectFieldList()
    {
        return new List<Schema.SObjectField> {
            Opportunity.AccountId,
            Opportunity.Amount,
            Opportunity.CloseDate,
            Opportunity.Description,
            Opportunity.ExpectedRevenue,
            Opportunity.Id,
            Opportunity.Name,
            Opportunity.Pricebook2Id,
            Opportunity.Probability,
            Opportunity.StageName,
            Opportunity.Type,
            Opportunity.DiscountType__c
        };
    }

    public Schema.SObjectType getSObjectType()
    {
        return Opportunity.sObjectType;
    }

    public List<Opportunity> selectById(Set<ID> idSet)
    {
        return (List<Opportunity>) selectSObjectsById(idSet);
    }

    public List<Opportunity> selectByIdWithProducts(Set<ID> idSet)
    {
        assertIsAccessible();

        OpportunityLineItemsSelector opportunityLineItemSelector = new OpportunityLineItemsSelector();
        PricebookEntriesSelector pricebookEntrySelector = new PricebookEntriesSelector();
        ProductsSelector productSelector = new ProductsSelector();
        PricebooksSelector pricebookSelector = new PricebooksSelector();

        opportunityLineItemSelector.assertIsAccessible();
        pricebookEntrySelector.assertIsAccessible();
        productSelector.assertIsAccessible();
        pricebookSelector.assertIsAccessible();

        String query = String.format(
                'select {0}, ' +
                  '(select {3},{5},{6},{7} ' +
                     'from OpportunityLineItems ' +
                     'order by {4}) ' +
                  'from {1} ' +
                  'where id in :idSet ' +
                  'order by {2}',
            new List<String>{
                getFieldListString(),
                getSObjectName(),
                getOrderBy(),
                opportunityLineItemSelector.getFieldListString(),
                opportunityLineItemSelector.getOrderBy(),
                pricebookEntrySelector.getRelatedFieldListString('PricebookEntry'),
                productSelector.getRelatedFieldListString('PricebookEntry.Product2'),
                pricebookSelector.getRelatedFieldListString('PricebookEntry.Pricebook2')
            });

        return (List<Opportunity>) Database.query(query);
    }
}

What we can find in the documentation is:

The selector layer offered by FFLIB brings things together and offers:

  • Centralised place for queries with common fields.
  • Allows joining other selectors so we can select common fields from other objects through relationship fields or even sub-selects.
  • Provides security checks to ensure that the user has access to a given sObject and all of the fields. If required, this can be disabled.

FFLib Selector Issues

Chapter 3 - FFLib Selector Issues.

The FFLib Selector has a few drawbacks that we will cover in this section.

Wrong Assumptions

FFLib Selector assumes that queries across the project are similar, so they should be kept in the Selector class for reuse.

The problem is that most queries on the project are unique and have complex conditions (WHERE). Even if the conditions are the same, additional SOQL clauses ( LIMIT, ORDER BY, OFFSET) may be required.

This means that queries are difficult to reuse.

Example

public List<OpportunityLineItem> selectRecentlyUsed(ID accountId, Integer recordLimit)
{
    assertIsAccessible();
    String query = String.format('select {0},{2},{3} FROM {1} WHERE Opportunity.Account.id = :accountId order by SystemModstamp DESC LIMIT :recordLimit',
        new List<String>{
            getFieldListString(),
            getSObjectName()
        });

    return (List<OpportunityLineItem>) Database.query(query);
}
  • What if I need ASC order? Should I create a similar method? Should I pass the order as a method parameter?
  • What if I create a new method based on my business requirement, and after a few months, I need to change it, but it is also used in other places? My update will affect others.

Keeping all queries (including those that are very business-specific) in the Selector class does not seem like a good idea.

God Class

God Class Antipatern

A Selector class can become huge and hard to read. Selectors for "popular" objects like Account, Contact, or Opportunity can have hundreds of lines of code.
This can be a problem, especially on projects with a lot of developers, as it leads to constant overwriting and merge conflicts.

Naming convention

public List<Account> selectByName(Set<String> names){
   fflib_QueryFactory query = newQueryFactory();
   query.addOrdering('Name', fflib_QueryFactory.SortOrder. ASCENDING);
   return (List<Account>) Database.query( query.toSOQL() );
}

selectByName is a simple example. Let's consider something more complicated:

public List<Opportunity> correctMethodName(Set<ID> idSet) {
    fflib_QueryFactory opportunitiesQueryFactory = newQueryFactory();

    fflib_QueryFactory lineItemsQueryFactory =
        new OpportunityLineItemsSelector().
            addQueryFactorySubselect(opportunitiesQueryFactory);

    new PricebookEntriesSelector().
        configureQueryFactoryFields(lineItemsQueryFactory, 'PricebookEntry');
    new ProductsSelector().
        configureQueryFactoryFields(lineItemsQueryFactory, 'PricebookEntry.Product2');
    new PricebooksSelector().
        configureQueryFactoryFields(lineItemsQueryFactory, 'PricebookEntry.Pricebook2');

    return (List<Opportunity>) Database.query(
        opportunitiesQueryFactory.setCondition('id in :idSet AND Amount > 100').toSOQL());
}

How would you name it? selectByIdWithProducts? But there is also Amount > 1000, so maybe selectByIdWithProductsWhereAmountIsGreaterThan100?

It's really difficult to come up with a proper method name when query conditions are complex. Mimicking all conditions in the method name also doesn't seem like a clean solution.

Inline Database.query

public with sharing class AccountSelector extends fflib_SObjectSelector {

   public Schema.SObjectType getSObjectType(){
      return Account.sObjectType;
   }

   public override List<Schema.SObjectField> getSObjectFieldList(){
      return new List<Schema.SObjectField> {
         Account.Id
      }
   }

   public List<Account> selectByName(Set<String> names){
      fflib_QueryFactory query = newQueryFactory();
      query.setCondition('Name IN :names');
      return (List<Account>) Database.query( query.toSOQL() );
   }
}

selectByName will be executed with sharing.
FFLib Selector has no logic that allows control over sharing rules.
Of course, you can have inherited sharing, but it will not cover cases where you need with sharing for most of the queries, but without sharing for some.

Inline binding

Let's take another look at selectByName.

public List<Account> selectByName(Set<String> names){
    fflib_QueryFactory query = newQueryFactory();
    query.setCondition('Name IN :names');
    return (List<Account>) Database.query( query.toSOQL() );
}

As you can see, the names variable is binded inline. FFLib Selector does not use the newest feature Database.queryWithBinds.

Entry threshold

The FFLib Selector is relatively complicated. Let's take a look:

public List<Opportunity> selectByIdWithProducts(Set<ID> idSet) {
    assertIsAccessible();

    OpportunityLineItemsSelector opportunityLineItemSelector = new OpportunityLineItemsSelector();
    PricebookEntriesSelector pricebookEntrySelector = new PricebookEntriesSelector();
    ProductsSelector productSelector = new ProductsSelector();
    PricebooksSelector pricebookSelector = new PricebooksSelector();

    opportunityLineItemSelector.assertIsAccessible();
    pricebookEntrySelector.assertIsAccessible();
    productSelector.assertIsAccessible();
    pricebookSelector.assertIsAccessible();

    String query = String.format(
                'select {0}, ' +
                  '(select {3},{5},{6},{7} ' +
                     'from OpportunityLineItems ' +
                     'order by {4}) ' +
                  'from {1} ' +
                  'where id in :idSet ' +
                  'order by {2}',
    new List<String>{
        getFieldListString(),
        getSObjectName(),
        getOrderBy(),
        opportunityLineItemSelector.getFieldListString(),
        opportunityLineItemSelector.getOrderBy(),
        pricebookEntrySelector.getRelatedFieldListString('PricebookEntry'),
        productSelector.getRelatedFieldListString('PricebookEntry.Product2'),
        pricebookSelector.getRelatedFieldListString('PricebookEntry.Pricebook2')
    });

    return (List<Opportunity>) Database.query(query);
}
  • Does it look clean to you?
  • Will a junior developer be able to understand and use it on the project?
  • The documentation does not contain examples and use cases.

Lack of newest features

Salesforce has released a few cool features that can be used in Selectors:

The FFLib community is huge, however, implementation of a new feature in such complex code will be difficult.

SOQL Lib

Chapter 4 - New Approach - SOQL Lib.

Assumptions

Based on the issues mentioned above, we tried to create something that allows managing queries in a better way.

Our SOQL Lib has a few assumptions:

  1. Small Selector Classes - the selector class should be small and contain ONLY query base configuration (fields, sharing settings) and very generic methods (byId, byRecordType, byAccountId).
  2. Build SOQL inline in a place of need - business-specific SOQLs should be built inline via SOQL builder in a place of need. Most of the queries on the project are case-specific (one-time) and are not generic. There is no need to keep them in the Selector class.
  3. Build SOQL dynamically via builder - developers should be able to adjust queries with specific fields, conditions, and other SOQL clauses. The SOQL Lib provides extensive SOQL API that guarantees great developer experience and flexibility.
  4. Do not spend time on selector methods naming - It can be difficult to find a proper name for a method in the selector class, especially for business-specific queries. It can be avoided by building SOQL inline in a place of need.
  5. Control FLS - the selector should allow controlling Field Level Security by simple methods like .systemMode().
  6. Control Sharing Rules - the selector should allow controlling Sharing Rules by simple methods like .withSharing() or .withoutSharing().
  7. Auto binding - the selector should be able to bind variables dynamically without additional effort from the developer's side - Database.queryWithBinds
  8. Mock results in Unit Tests - the selector should allow for mocking data in unit tests.
  9. Lib should be simple - the selector lib should have a quick start and be easy to understand, even by junior developers.
  10. Do not be afraid of changes - developers can do whatever is needed with inline queries. It will not affect others.

The SOQL Library consists of:

  • SOQL Builder
  • SOQL Selector

SOQL Builder

The SOQL Builder allows building a query dynamically and executing it.

SOQL query = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name, Account.Industry);

Assert.areEqual('SELECT Id, Name, Industry FROM Account', query.toString());

SOQL Selector

A selector layer contains code responsible for querying records from the database. Although you can place SOQL queries in other layers, a few things can happen as the complexity of your code grows. ~ Salesforce

SOQL Lib provides a whole new concept for Selector's usage.

Most of the SOQLs on the project are one-time queries executed for specific business cases.
We recommend keeping only very generic methods in the selector class; all others can be built in a place of usage.

Basic Features

Dynamic SOQL

The SOQL.cls class provides methods for building SOQL clauses dynamically.

SOQL query = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .setLimit(100);

Assert.areEqual('SELECT Id, Name FROM Account LIMIT 100', query.toString());

Automatic binding

All variables used in the WHERE condition are automatically binded.

SOQL query = SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .whereAre(SOQL.Filter.name().contains('Test'));

Assert.areEqual('SELECT Id, Name FROM Account WHERE Name = :v1', query.toString());
// Binding Map
{
  "v1" : "%Test%"
}

Control FLS

AccessLevel Class

Object permissions and field-level security are controlled by the lib. Developers can change FLS settings to match business requirements.

User mode

By default, all queries are executed in AccessLevel.USER_MODE.

The object permissions, field-level security, and sharing rules of the current user are enforced.

System mode

Developers can change the mode to AccessLevel.SYSTEM_MODE by using the .systemMode() method.

The object and field-level permissions of the current user are ignored, and the record-sharing rules are controlled by the sharingMode.

// SELECT Id, Name FROM Account - skip FLS
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .systemMode()
    .toList();

Control Sharings

Use the with sharing or without sharing keywords on a class to specify whether sharing rules must be enforced. Use the inherited sharing keyword on a class to run the class in the sharing mode of the class that called it.

with sharing

By default, all queries are executed with sharing, enforced by AccessLevel.USER_MODE.

AccessLevel.USER_MODE enforces object permissions and field-level security.

The developer can skip FLS by adding .systemMode() and .withSharing().

// Query executed in without sharing
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .systemMode()
    .withSharing()
    .toList();

without sharing

The developer can control sharing rules by adding .systemMode() (record sharing rules are controlled by the sharingMode) and .withoutSharing().

// Query executed in with sharing
SOQL.of(Account.SObjectType)
    .with(Account.Id, Account.Name)
    .systemMode()
    .withoutSharing()
    .toList();

inherited sharing

The developer can control sharing rules by adding .systemMode() (record sharing rules are controlled by the sharingMode) by default it is inherited sharing.

public inherited sharing class SOQL_Account implements SOQL.Selector {
    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Name, Account.AccountNumber)
            .systemMode();
    }
}

Mocking

Mocking provides a way to substitute records from a Database with some prepared data.

Mocked queries won't make any SOQL queries and simply return the data set in the method definition. Mocking ignores all filters and relations, and what is returned depends solely on the data provided to the method. Mocking only works during test execution. To mock the SOQL query, use the .mockId(id) method to make it identifiable. If you mark more than one query with the same ID, all marked queries will return the same data.

public with sharing class ExampleController {

    public static List<Account> getPartnerAccounts(String accountName) {
        return SOQL_Account.query()
            .with(Account.BillingCity, Account.BillingCountry)
            .whereAre(SOQL.FilterGroup
                .add(SOQL.Filter.name().contains(accountName))
                .add(SOQL.Filter.recordType().equal('Partner'))
            )
            .mockId('ExampleController.getPartnerAccounts')
            .toList();
    }
}
@IsTest
private class ExampleControllerTest {

    @IsTest
    static void getPartnerAccounts() {
        List<Account> accounts = new List<Account>{
            new Account(Name = 'MyAccount 1'),
            new Account(Name = 'MyAccount 2')
        };

        SOQL.setMock('ExampleController.getPartnerAccounts', accounts);

        // Test
        List<Account> result = ExampleController.getPartnerAccounts('MyAccount');

        Assert.areEqual(accounts.size(), result.size());
    }
}

Avoid duplicates

Generic SOQLs can be kept in the selector class.

public inherited sharing class SOQL_Account implements SOQL.Selector {

    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Name, Account.AccountNumber)
            .systemMode()
            .withoutSharing();
    }

    public static SOQL byRecordType(String rtDevName) {
        return query()
            .with(Account.BillingCity, Account.BillingCountry)
            .whereAre(SOQL.Filter.recordType().equal(rtDevName));
    }

    public static SOQL byName(String accountName) {
        return query()
            .whereAre(SOQL.Filter.name().equal(accountName));
    }

    public static SOQL getPartners() {
        return byRecordType('Partner');
    }
}

Default configuration

The selector class can provide default SOQL configuration, e.g., default fields, FLS settings, and sharing rules.

public inherited sharing class SOQL_Account implements SOQL.Selector {

    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Id, Account.Name) // default fields
            .systemMode(); // default FLS mode
    }
}

How to build?

Our Lib does NOT provide one method to build selectors.
Choose an approach that meets your needs. The SOQL.cls code does not force you to any specific selector implementation.

Interface + static (Recommended)

public inherited sharing class SOQL_Account implements SOQL.Selector {
    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Name, Account.AccountNumber)
            .systemMode()
            .withoutSharing();
    }

    public static SOQL byRecordType(String rt) {
        return query()
            .with(Account.BillingCity, Account.BillingCountry)
            .whereAre(SOQL.Filter.recordType().equal(rt));
    }
}
public with sharing class ExampleController {

    public static List<Account> getAccounts(String accountName) {
        return SOQL_Account.query()
            .with(Account.BillingCity, Account.BillingCountry)
            .whereAre(SOQL.Filter.name().contains(accountName))
            .toList();
    }

    public static List<Account> getAccountsByRecordType(String recordType) {
        return SOQL_Account.byRecordType(recordType)
                .with(Account.ParentId)
                .toList();
    }
}

More examples you can find in our documentation.

📣 NOTE! Still not convinced about the new approach? No problem. You can keep all queries inside the Selector class. The SOQL Lib is a flexible solution that you can use however you want.

SOQL Design

Single class

SOQL Lib is a single-class solution.

You don't need to think about dependencies; everything you need is stored in SOQL.cls. The SOQL.cls only takes around 1500 lines of code.

Different clauses are encapsulated in small, inner classes.
All crucial information is kept at the top of the class, so developers can use it even without reading the documentation.

    public static SubQuery SubQuery {
        get {
            return new QSubQuery();
        }
    }

    public static FilterGroup FilterGroup { // A group to nest more filters
        get {
            return new QFilterGroup();
        }
    }

    public static Filter Filter {
        get {
            return new QFilter();
        }
    }

    public static InnerJoin InnerJoin {
        get {
            return new QJoinQuery();
        }
    }

    public interface Selector {
        Queryable query();
    }

    public interface Queryable {
        Queryable of(SObjectType ofObject);
        Queryable of(String ofObject); // Dynamic SOQL

        Queryable with(SObjectField field);
        Queryable with(SObjectField field1, SObjectField field2);
        Queryable with(SObjectField field1, SObjectField field2, SObjectField field3);
        Queryable with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
        Queryable with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);
        Queryable with(List<SObjectField> fields); // For more than 5 fields
        Queryable with(String fields); // Dynamic SOQL
        Queryable with(SObjectField field, String alias); // Only aggregate expressions use field aliasing
        Queryable with(String relationshipName, SObjectField field);
        Queryable with(String relationshipName, SObjectField field1, SObjectField field2);
        Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3);
        Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
        Queryable with(String relationshipName, SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);
        Queryable with(String relationshipName, List<SObjectField> fields); // For more than 5 fields
        Queryable with(SubQuery subQuery); // SOQL.SubQuery

        Queryable count();
        Queryable count(SObjectField field);
        Queryable count(SObjectField field, String alias);

        Queryable delegatedScope();
        Queryable mineScope();
        Queryable mineAndMyGroupsScope();
        Queryable myTerritoryScope();
        Queryable myTeamTerritoryScope();
        Queryable teamScope();

        Queryable whereAre(FilterGroup filterGroup); // SOQL.FilterGroup
        Queryable whereAre(Filter filter); // SOQL.Filter
        Queryable whereAre(String conditions); // Conditions to evaluate

        Queryable groupBy(SObjectField field);
        Queryable groupByRollup(SObjectField field);

        Queryable orderBy(String field); // ASC, NULLS FIRST by default
        Queryable orderBy(String field, String direction); // dynamic order by, NULLS FIRST by default
        Queryable orderBy(SObjectField field); // ASC, NULLS FIRST by default
        Queryable orderBy(String relationshipName, SObjectField field); // ASC, NULLS FIRST by default
        Queryable sortDesc();
        Queryable nullsLast();

        Queryable setLimit(Integer amount);

        Queryable offset(Integer startingRow);

        Queryable forReference();
        Queryable forView();
        Queryable forUpdate();
        Queryable allRows();

        Queryable systemMode(); // USER_MODE by default

        Queryable withSharing(); // Works only with .systemMode()
        Queryable withoutSharing(); // Works only with .systemMode()

        Queryable mockId(String id);

        Queryable preview();

        Queryable stripInaccessible();
        Queryable stripInaccessible(AccessType accessType);

        Queryable byId(SObject record);
        Queryable byId(Id recordId);
        Queryable byIds(Iterable<Id> recordIds); // List or Set
        Queryable byIds(List<SObject> records);

        String toString();
        Object toValueOf(SObjectField fieldToExtract);
        Set<String> toValuesOf(SObjectField fieldToExtract);
        Integer toInteger(); // For COUNT query
        SObject toObject();
        List<SObject> toList();
        List<AggregateResult> toAggregated();
        Map<Id, SObject> toMap();
        Database.QueryLocator toQueryLocator();
    }

    public interface SubQuery { // SOQL.SubQuery
        SubQuery of(String ofObject);

        SubQuery with(SObjectField field);
        SubQuery with(SObjectField field1, SObjectField field2);
        SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3);
        SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4);
        SubQuery with(SObjectField field1, SObjectField field2, SObjectField field3, SObjectField field4, SObjectField field5);
        SubQuery with(List<SObjectField> fields); // For more than 5 fields
        SubQuery with(String relationshipName, List<SObjectField> fields);
        SubQuery with(SubQuery subQuery); // SOQL.SubQuery

        SubQuery whereAre(FilterGroup filterGroup); // SOQL.FilterGroup
        SubQuery whereAre(Filter filter); // SOQL.Filter

        SubQuery orderBy(SObjectField field);
        SubQuery orderBy(String relationshipName, SObjectField field);
        SubQuery sortDesc();
        SubQuery nullsLast();

        SubQuery setLimit(Integer amount);

        SubQuery offset(Integer startingRow);

        SubQuery forReference();
        SubQuery forView();
    }

    public interface FilterGroup { // SOQL.FilterGroup
        FilterGroup add(FilterGroup filterGroup); // SOQL.FilterGroup
        FilterGroup add(Filter filter); // SOQL.Filter
        FilterGroup add(String dynamicCondition); // Pass condition as String

        FilterGroup anyConditionMatching(); // All group filters will be join by OR
        FilterGroup conditionLogic(String order);

        Boolean hasValues();
    }

    public interface Filter { // SOQL.Filter
        Filter id();
        Filter recordType();
        Filter name();
        Filter with(SObjectField field);
        Filter with(String field);
        Filter with(String relationshipName, SObjectField field);

        Filter isNull(); // = NULL
        Filter isNotNull(); // != NULL
        Filter isTrue(); // = TRUE
        Filter isFalse(); // = FALSE
        Filter equal(Object value); // = :value
        Filter notEqual(Object value); // != :value
        Filter lessThan(Object value); // < :value
        Filter greaterThan(Object value); // > :value
        Filter lessOrEqual(Object value); // <= :value
        Filter greaterOrEqual(Object value); // >= :value
        Filter containsSome(List<String> values); // LIKE :values
        Filter contains(String value); // LIKE :'%' + value + '%'
        Filter endsWith(String value); // LIKE :'%' + value
        Filter startsWith(String value); // LIKE :value + '%'
        Filter contains(String prefix, String value, String suffix); // custom LIKE
        Filter isIn(Iterable<Object> iterable); // IN :inList or inSet
        Filter isIn(List<Object> inList); // IN :inList
        Filter isIn(InnerJoin joinQuery); // SOQL.InnerJoin
        Filter notIn(Iterable<Object> iterable); // NOT IN :inList or inSet
        Filter notIn(List<Object> inList); // NOT IN :inList
        Filter notIn(InnerJoin joinQuery); // SOQL.InnerJoin
        Filter includesAll(Iterable<String> values); // join with ;
        Filter includesSome(Iterable<String> values); // join with ,
        Filter excludesAll(Iterable<String> values); // join with ,
        Filter excludesSome(Iterable<String> values);  // join with ;

        Filter removeWhenNull(); // Condition will be removed for value = null

        Boolean hasValue();
    }

    public interface InnerJoin { // SOQL.InnerJoin
        InnerJoin of(SObjectType ofObject);

        InnerJoin with(SObjectField field);

        InnerJoin whereAre(FilterGroup filterGroup); // SOQL.FilterGroup
        InnerJoin whereAre(Filter filter); // SOQL.Filter
    }

    @TestVisible
    private static void setMock(String mockId, SObject record) {
        setMock(mockId, new List<SObject>{ record });
    }

    @TestVisible
    private static void setMock(String mockId, List<SObject> records) {
        mock.setMock(mockId, records);
    }

    @TestVisible
    private static void setCountMock(String mockId, Integer amount) {
        mock.setCountMock(mockId, amount);
    }

Functional Programming

SOQL Lib uses the concept called Apex Functional Programming.

You can see an example of it with SOQL.SubQuery, SOQL.FilterGroup, SOQL.Filter and SOQL.InnerJoin.
Those classes encapsulate the logic, and only necessary methods are exposed via interfaces.

Queryable whereAre(FilterGroup filterGroup); // SOQL.FilterGroup
Queryable whereAre(Filter filter); // SOQL.Filter
SOQL.of(Account.SObjectType)
     .with(Account.Id, Account.Name);
     .whereAre(SOQL.FilterGroup
        .add(SOQL.Filter.id().equal(accountId))
        .add(SOQL.Filter.with(Account.Name).contains(accountName))
        .anyConditionMatching() // OR
      )
     .toList();

Composition over inheritance

SOQL Lib's Selector follows the rule of Composition over inheritance.

Instead of using inheritance:

public with sharing class OpportunitiesSelector extends SObjectSelector {
    // ...
}

We use composition:

public inherited sharing class SOQL_Account implements SOQL.Selector {
    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Name, Account.AccountNumber)
            .systemMode()
            .withoutSharing();
    }
}

This approach gives us a lot of flexibility. We can set not only default fields, FLS, and sharing rules but also add default conditions that will be used in every query.

Return SOQL instance

public inherited sharing class AccountSelector implements SOQL.Selector {
    public static SOQL query() {
        return SOQL.of(Account.SObjectType)
            .with(Account.Name, Account.AccountNumber)
            .systemMode();
    }

    public static SOQL byRecordType(String rt) {
        return query()
            .with(Account.BillingCity, Account.BillingCountry)
            .whereAre(SOQL.Filter.recordType().equal(rt));
    }
}

As you can see, the method byRecordType(String rt) returns an instance of SOQL instead of List<SObject> or Object. Why is that? You can adjust the query to your needs and add more SOQL clauses without duplicating the code.

AccountSelector.byRecordType(recordType).with(Account.ParentId).toList(); // additional field
AccountSelector.byRecordType(recordType).orderBy(Account.Name).toList(); // additionl order by name

SOQL API

We tried to make SOQL API as similar as possible to standard SOQL.

Documentation


If you have any questions, feel free to ask in the comment section below. 🙂

Was it helpful? Start SOQL Lib on Github and check out our other great posts here.


Resources

Buy Me A Coffee