D'fflib
HOWLayers

Selectors

Step-by-step guide on how to set up and interact with a Selector, maximise the functions and benefits, while being aware of edge cases and D'fflib specific functionalities.

Characteristics

A Selector is the one place that centralises and knows how to read a single SObject: which fields, which filters, which security context. It answers "which records?" without knowing who asked or why. Think of a librarian: it fetches exactly what you request and never decides what you do with it.

Responsibilities

Selectors implement the Selector/Repository pattern, with the main responsibilities to:

Centralise construction and execution of all SOQL queries for one SObject.
Centralise and standardise security enforcement.
Enable mockable queries in unit tests.

Separation of concerns in D'fflib

With the dfflib_Database proxy layer , D'fflib separates the concerns. Where originally, Selectors also performed the effective query, this has now been separated. Making Selectors more caller-agnostic, since methods can be reused across different context-needs. By allowing the calling class to define the Context, Selectors no longer need to differentiate, including the repetitive withSharing Booleans and inner-WithoutSharing-classes.

  • Selectors focus on capturing 'how' to retrieve (security settings),
  • QueryFactories focus on 'what' to retrieve (fields and conditions),
  • Database proxies interact with the database.

Note, SOSL queries aren't natively supported in (D')fflib, as one can span multiple SObject types, and therefore don't fit in Selectors. If you need a SOSL query, you can consider a SOSL Service class to enable mocking. Only when the SOSL is, and will remain, limited to one SObjectType, you're free to place this in the Selector for that specific SObjectType.

Interactions

InteractionLayers
Called byEntrypoints, Domains, Services and Trigger Handlers
CallingN/A

By design, there should be no need for a Selector to call another layer, to meet its responsibilities. Other Selectors can be referenced for subselects, but those don't require a selector initiation. Domains and Services contain business logic and should not be called from a Selector, as that would violate the separation of concerns.

Due to this setup Selector methods should be caller agnostic and not assume any context of the caller. This implies the conversion from a List<SObject> to a wrapper class should be performed in the calling class and not in the Selector.

Rules

  • Methods should be caller agnostic and not assume any context of the caller.
  • Methods should be bulkified and accept a Set<Id> or List<SObject> as input.
  • Methods should return a caller-agnostic List and not a wrapper class. In edge-cases one could opt to return SObject to centralise the .isEmpty() or .size() == 1 check, though an Exception should be thrown when multiple or no records are found.
  • Method names should be descriptive and indicate the purpose of the query, e.g. selectByRoomId or selectByStatusAndDateRange.

Initiation

How to call

Selectors are always instantiated via the (class-specific) static newInstance() factory method. For D'fflib we highly recommend to always pass the dfflib_Database.Context to the factory method, as this enables the caller to control the query security context.

// Always pass the Database Context — the caller decides security, not the method
List<RoomReservation__c> reservations = SEL_RoomReservations
    .newInstance( dfflib_Database.Context.FULL )
    .selectByRoomIds( roomIds );

→ Full table of Database Context options

Class boilerplate

Below snippet shows the boilerplate code for a Selector class, extending the framework-base-class and implementing the required newInstance(), getSObjectType() and getSObjectFieldList() methods. getRelatedFieldSet() is an optional method to auto-include parent fields to selector methods.

SEL_RoomReservations.cls
// Comments are only to elaborate and don't follow JavaDoc standard, so should be removed when copying.
public with sharing class SEL_RoomReservations extends dfflib_SObjectSelector{
    // Factory method to instantiate the selector
    public static SEL_RoomReservations newInstance( dfflib_Database.Context ctx ){
        return ( SEL_RoomReservations ) dfflib.selector.newInstance( RoomReservation__c.SObjectType, ctx );
    }

    // Returns the SObject type this selector operates on
    public override Schema.SObjectType getSObjectType(){
        return RoomReservation__c.SObjectType;
    }

    // Base fields always included in every query from this selector
    public override List<Schema.SObjectField> getSObjectFieldList(){
        return new List<Schema.SObjectField>{
            RoomReservation__c.Id,
            RoomReservation__c.Contact__c,
            RoomReservation__c.Room__c,
            RoomReservation__c.Status__c
        };
    }

    // optional, auto-include parent fields without subselect
    public override Set<String> getRelatedFieldSet(){
        return new Set<String>{
            'Room__r.Name',
            'Room__r.Capacity__c'
        };
    }
}
  • There is no framework need to construct an interface like ISelector. Examples including such, predate the Apex Stub API to enable mocking.

The framework factory (dfflib.selector) will:

  1. Check whether a mock-instance was set and return if available
  2. Retrieve the related Dfflib_Selector_Config__mdt metadata
  3. Instantiate the Selector class with the retrieved configuration.
  4. Return the constructed Selector class-instance.

Theoretically, it is possible to call dfflib.selector.newInstance() directly to avoid the static newInstance() boilerplate in your Selector. However, you'll need to cast its generic dfflib_SObjectSelector result and repeat the SObjectType argument that the Selector class would otherwise define centrally.

Regardless of choice, the recommendation is to be consistent throughout your codebase.

SEL_RoomReservations selectorDirect = ( SEL_RoomReservations ) dfflib.selector.newInstance( RoomReservation__c.SObjectType, dfflib_Database.Context.FULL );

Metadata registration

To ensure the newInstance() factory method resolves the correct class, the Selector must be registered in the Dfflib_Selector_Config__mdt custom metadata type . With the right AI skills, this metadata file can be automatically generated, else ensure to set the below values:

FieldPurposeExample
MasterLabelLabel of the mdt record. Shown in SetupRoom Reservation
DeveloperNameUnique API name of the record. Doesn't allow duplicate (___) thus write custom SObjectTypes as _cRoomReservation_c
SObjectType__cAPI name of the SObjectRoomReservation__c
Implementation_Type__cClass implementing the SelectorSEL_RoomReservations

Note, this registers the Selector to the SObjectType, implying that - for now - it's not possible to have multiple Selectors for the same SObjectType.


Standard methods

Selector methods follow a consistent pattern:

  1. Initiate a QueryFactory
  2. Set the desired fields, conditions, grouping etc.
  3. Return the database response

It's a public method that returns a List<SObject> and optionally accepts input parameters for dynamic conditions, which should always be fit for future.

public List<RoomReservation__c> selectByRoomIds( Set<Id> roomIds ){
    dfflib_QueryFactory qf = newQueryFactory();
    qf.setCondition( 'Room__c IN :roomIds' );

    return this.executeQuery( qf, new Map<String, Object>{
        'roomIds' => roomIds
    } );
    // qf.toSOQL():
    //  SELECT  Id, Name, Room__c, Status__c 
    //   FROM   RoomReservation__c 
    //   WHERE  Room__c IN :roomIds ORDER BY Name
}

1. Initiate query factory

The framework provides multiple options to simplify initiation of a dfflib_QueryFactory via the Selector's newQueryFactory() method. Enabling easy control whether or not selector-defined-fields should be included in that particular method

SignatureincludeSelectorFieldsincludeRelatedFieldSetFieldsWhen to use
newQueryFactory()truefalseDefault/Recommended, to auto-include configured fields
newQueryFactory( false )falsefalseFull control on included fields, excluding standard set. E.g. for aggregate queries or access-restricted users (Community Users)
newQueryFactory( true, true ) truetrueWhen you also need parent fields from getRelatedFieldSet() (e.g. Room__r.Name)

With above signatures the original security parameters (assertCRUD / enforceFLS) will always default to null, which causes code to skip the original logic as this now is governed by dfflib_Database.Context which is set upon selector construction, not per query call.

Note, original signatures still exist for backward compatibility, but are recommended to only be used in existing code or unique edge cases.

SignatureassertCRUDenforceFLSincludeSelectorFieldsincludeRelatedFieldSetFields
newQueryFactory( assertCRUD, enforceFLS, includeSelectorFields )callercallercallerfalse
newQueryFactory( assertCRUD, enforceFLS, includeSelectorFields, includeRelatedFieldSetFields )callercallercallercaller
newQueryFactory( assertCRUD, enforceFLS, includeSelectorFields, dataAccess )callercallercallerfalse
  • assertCRUD: When true, objType.getDescribe().isAccessible() is validated before querying, optionally throwing a dfflib_SObjectDomain.DomainException.
  • enforceFLS: When true, performs objType.getDescribe().getField( fieldName ).getDescribe().isAccessible() for each field, optionally throwing an fflib_SecurityUtils.FlsException when running user has no access.
  • Optionally setDataAccess(DataAccess) could be set on the selector which will override the default enforceFLS based on the enum-value (USER_MODE or SYSTEM_MODE).

Legacy DataAccess mapping
A legacy DataAccess value resolves to a dfflib_Database.Context:

DataAccessdfflib_Database.Context
LEGACYINHERIT
USER_MODEFULL
SYSTEM_MODEINHERIT

fflib applies WITH SYSTEM_MODE, which skips CRUD/FLS, and leaves record sharing to the sharing keyword of the selector class that runs the query. INHERIT resolves the same way, through the inherited sharing query proxy.

One difference from fflib: a without sharing selector calling an inherited base method (e.g. selectSObjectsById()) now runs without sharing, where fflib's with sharing base class applied sharing. Selectors that rely on seeing all records should pass Context.NONE explicitly.

2. Set query context

A SOQL query typically consists of three parts: SELECT (fields & subselects), WHERE conditions and ORDER BY. Below sections introduce the framework methods relevant per part of the query and how to properly apply them. Lesser common queries are detailed in the Advanced section like aggregate queries with GROUP BY and HAVING, or queries with FOR etc.

SELECT fields

When you need additional fields than listed in getSObjectFieldList(), you can call:

  • selectFields() or selectField()
    which both accept SObjectField and String field references, and the plural method accepts both Lists and Sets. SObjectField tokens are preferred, given their compilation validation which prevents against unconscious deletions. For the plural method Sets are preferred, given their unique-characteristics.
SEL_RoomReservations.selectByRoomId
dfflib_QueryFactory qf = newQueryFactory( false );
qf.selectField( RoomReservation__c.Room__c );
qf.selectFields( new List<Schema.SObjectField>{
    RoomReservation__c.CheckInDate__c,
    RoomReservation__c.CheckOutDate__c
} );
// SELECT Room__c, CheckInDate__c, CheckOutDate__c FROM RoomReservation__c

String field references enable inclusion of parent-object-fields or SOQL aggregate functions with aliases.

SEL_RoomReservations.selectOpenCountPerRoomId
qf.selectField( 'Contact__r.Name' );                                     // cross-object (parent field)
qf.selectFields( new Set<String>{ 'COUNT( Id ) numOpenReservations' } ); // aggregate function with alias
  • selectFieldSet( Schema.FieldSet )
    If the environment leverages FieldSets, these can be easily included to queries. Given the usage of Sets in QueryFactory, there is no need to write logic to prevent duplicate field inclusions.

SELECT subselects

To reduce the number of queries, SOQL allows you to query child-records while retrieving the parent record. In one query it's thus possible to query the record itself, fields from a parent record and some child records. E.g. fetching a Contact with parent Account segmentation and all future RoomReservation__c records.

The fflib framework supports two ways to configure subselects, for different use-cases and preferences.

  • query.subselectQuery( relationship )
    Accepts either the String relationship name or Schema.ChildRelationship.
    Returns a blank dfflib_QueryFactory based on the relationship name, only inheriting parents' FLS and dfflib_Database.Context setup. This gives more control on the constructed subselect, and allows subselects for SObjectTypes with no Selector class (yet).

  • new SEL_RoomReservations().addQueryFactorySubselect( parentQueryFactory, relationshipName )
    Accepts the parent QueryFactory and String relationship name. Returns a (default) configured dfflib_QueryFactory, calling configureQueryFactory() which automatically includes getSObjectFieldList() fields, default ORDER BY and security settings based on inherited values from dfflib_SObjectSelector. This puts the selector class as the source of truth, which does imply that if any new field is added to the selector, it will automatically be added to all such subselect queries. This method does call subselectQuery() internally, implying there is no distinction in e.g. the mock-possibilities.

SEL_Rooms
dfflib_QueryFactory qf = newQueryFactory( false );
dfflib_QueryFactory subQF = qf.subselectQuery( 'Reservations__r' );
subQF.selectFields( new List<String>{ 'Id', 'Status__c', 'CheckInDate__c' } );
// SELECT Id, 
//        (SELECT id, status__c, checkindate__c FROM Reservations__r)
//  FROM Room__c ORDER BY Name ASC NULLS FIRST

dfflib_QueryFactory qf = newQueryFactory( false );
dfflib_QueryFactory subQF = new SEL_RoomReservations().addQueryFactorySubselect( qf, 'Reservations__r' );
// SELECT Id, 
//        (SELECT id, name, checkindate__c, checkoutdate__c, contact__c, ... FROM Reservations__r ORDER BY Name ASC NULLS FIRST )
//  FROM Room__c ORDER BY Name ASC NULLS FIRST

Note, for full flexibility fflib includes:

  • addQueryFactorySubselect(parentQueryFactory, relationshipName, includeSelectorFields)
    to control whether default fields are included
  • addQueryFactorySubselectNoAccessChecks(parentQueryFactory, relationshipName, includeSelectorFields)
    to allow disabling assertCRUD validation in subselect execution

This implies that one could realise the exact same query with both approaches:

qf.subselectQuery( 'Reservations__r' );
new SEL_RoomReservations().addQueryFactorySubselectNoAccessChecks( qf, 'Reservations__r', false ).emptyOrdering();
// SELECT Id, (SELECT Id FROM Reservations__r)  FROM Room__c ORDER BY Name ASC NULLS FIRST

Salesforce subselect governor limits

Each SOQL query allows up to 20 subselects, with a limit of 50,000 records for all records (including child-records). Per API 58.0 (Summer '23) and 61.0 (Summer '24), Salesforce loosened the limit of two levels (one subselect) to five levels (record + 4 levels of nested subselect), first for REST and SOAP API calls, then for Apex.


WHERE conditions

To retrieve only the desired records, it's best practice to define a WHERE clause. Due to the endless possibilities, the framework does not enforce a specific structure, but instead expects a single String with the full condition.

  • setCondition( String )
    Directly sets the WHERE clause, where bind-variables (:varName) should be used to mitigate SOQL injections and optimise query plan caching.
SEL_RoomReservations.selectByRoomId
dfflib_QueryFactory qf = newQueryFactory();
qf.setCondition( 'Room__c IN :roomIds' );

return this.executeQuery( qf, new Map<String, Object>{
    'roomIds' => roomIds
} );
// SELECT Id FROM RoomReservation__c WHERE Room__c IN :roomIds ORDER BY Name

Multiple conditions
To set multiple conditions, each project can decide their preference for condition construction, as long as there is consistency in the codebase. Our preference is to construct a List<String> and combine this with String.join(). Based on the 'expensive' nature of String concatenation in Apex, and the ease of adding conditions dynamically to the list before joining.

SEL_RoomReservations.selectOpenCountPerRoomId
dfflib_QueryFactory qf = newQueryFactory();
List<String> conditions = new List<String>{
    'Room__c IN :roomIds',
    'Status__c IN :openStatuses'
};
//if( isFutureReservation ){
//    conditions.add( 'CheckInDate__c >= :startDate' );
//}
qf.setCondition( String.join( conditions, ' AND ' ) );

return this.executeQuery( qf, new Map<String, Object>{
    'roomIds'      => roomIds,
    'openStatuses' => DOM_RoomReservations.STATUSES_OPEN
} );
// SELECT Id FROM RoomReservation__c WHERE Room__c IN :roomIds AND Status__c IN :openStatuses ORDER BY Name

WHERE ... IN (SELECT ...)
Relating the previous two sections, the framework fully supports to set a subselect SOQL condition, via the WHERE ... IN (SELECT ...) syntax, but doesn't enforce a specific structure, as this is highly dependent on the use-case and project standards.

Possible method for Rooms Selector
public List<Room__c> selectRoomsWithFutureReservations( Date startDate ){
    dfflib_QueryFactory qf = newQueryFactory();
    List<String> conditions = new List<String>{
        'Status__c IN :openStatuses',
        'Id IN ( SELECT Room__c FROM RoomReservation__c WHERE CheckInDate__c >= :startDate )',
    };
    qf.setCondition( String.join( conditions, ' AND ' ) );

    return this.executeQuery( qf, new Map<String, Object>{
        'startDate'    => startDate,
        'openStatuses' => DOM_Rooms.STATUSES_OPEN
    } );
    // SELECT Id FROM Room__c WHERE Status__c IN :openStatuses 
    //    AND Id IN ( SELECT Room__c FROM RoomReservation__c WHERE CheckInDate__c >= :startDate )
    //    ORDER BY Name
}

ORDER BY

For data consistency across sequential query-executions, the QueryFactory applies a default getOrderBy(). This is the Name field, when available and not encrypted, or CreatedDate, and as final fallback Id.

The SOQL order is kept as a List of Ordering instances, default with the first value which is set in newQueryFactory(). Therefore, one should be cautious when adding additional orderings, and consider the relevant function:

  • addOrdering appends to existing ORDER BY (including the default)
  • setOrdering replaces existing orderings (including the default) with a single ordering
  • emptyOrdering clears all orderings, to e.g. prevent SOQL validation errors for GROUP BY

Each method accepts:

  • Schema.SObjectField (recommended) or String field names (for parent-fields and aliases/expressions)
  • dfflib_QueryFactory.SortOrder enum for ASCENDING or DESCENDING direction
  • Optional nullsLast boolean to explicitly set NULLS FIRST/LAST. Default false, as per Salesforce default
Ordering signature examples
// Appends to existing ORDER BY (RoomReservation__c.Name ASC NULLS FIRST)
qf.addOrdering( RoomReservation__c.CheckInDate__c, dfflib_QueryFactory.SortOrder.ASCENDING );       // NULLS FIRST
qf.addOrdering( RoomReservation__c.TotalPrice__c, dfflib_QueryFactory.SortOrder.DESCENDING, true ); // NULLS LAST

// Replaces existing orderings (including the default) with a single clause
qf.setOrdering( RoomReservation__c.Status__c, dfflib_QueryFactory.SortOrder.ASCENDING );

// Removes existing orderings entirely
// E.g. avoid SOQL validation error for `GROUP BY` when default Order by field isn't grouped
qf.emptyOrdering();

3. Execute query

Once the dfflib_QueryFactory is configured, specify the execution method that matches the query type. All variants accept either a dfflib_QueryFactory instance or a raw SOQL string, with an optional bind-variable map. Ensure the selector methods' return type matches that of the framework's method.

It's recommended to put this. upfront to indicate the method is inherited from dfflib_SObjectSelector.

MethodReturnsWhen to use
executeQuery( qf, bindVars )List<SObject>Standard SELECT execution for record-returning methods
executeAggregateQuery( qf, bindVars )List<dfflib_AggregateResult>Queries with GROUP BY or aggregate functions. Note, default returns dfflib_AggregateResult which can be mocked in unit tests
getQueryLocator( qf, bindVars )Database.QueryLocatorCommonly used in Batch classes to iterate over large data sets
getCursor( qf, bindVars )Database.CursorCursor-based pagination outside Batch

Original fflib setup

D'fflib provides above four methods to offload the effective Database.query operation and with that grant flexibility to control the context the query runs with and thus, unlock mocking in unit tests.

Originally, the last statement in a selector method would be return Database.query( qf.toSOQL() ), which caused the query to run in the context of the selector class, and required bind-variables to exist on execution.


Advanced methods

ALL ROWS

In Apex SOQL queries it's possible to retrieve "all records", including records from Recycle Bin and archived Tasks or Events, by appending ALL ROWS. This is comparable to the queryAll() option via API, and enables the WHERE IsDeleted = true condition, which normally returns an empty data set.

qf.setAllRows( true );

Aggregate queries (GROUP BY, HAVING, SOQL functions)

When in need for a SUM, COUNT, MIN or MAX, you can query all records and loop in Apex, but when not needing the individual records it is more efficient to let SOQL perform the logic with aggregate queries and keep code cleaner.

These queries are set up the same way as standard queries (select, condition, group by), but have a couple considerations:

  • Fields should be consciously added to avoid a SOQL error , thus excluding default fields (newQueryFactory( false ))
  • SOQL functions and aliases can be added via the standard (selectFields( new Set<String>{ 'COUNT( Id ) numOpenReservations } );)
  • setGroupByField() registers the GROUP BY field. This automatically clears the ORDER BY by default (unless true is passed for the preserveOrderings-variable)
  • The results are dfflib_AggregateResult rows, thus not SObject records, which implies values are accessed by alias String rather than field token (dfflib_AggregateResult.get( 'numOpenReservations' )).

Below are two examples to share some use-cases, method signatures and how to properly implement, call and mock dfflib_AggregateResult. Use the tabs to navigate between the selector, calling and unit test method and the database return format.

Example 1: COUNT of records for one GROUP BY field

SEL_RoomReservations.selectOpenCountPerRoomId
public List<dfflib_AggregateResult> selectOpenCountPerRoomId( Set<Id> roomIds ){
    dfflib_QueryFactory qf = newQueryFactory( false );
    qf.selectField( RoomReservation__c.Room__c );
    qf.selectField( 'COUNT( Id ) numOpenReservations' );
    List<String> conditions = new List<String>{
        'Room__c IN :roomIds',
        'Status__c IN :openStatuses'
    };
    qf.setCondition( String.join( conditions, ' AND ' ) );
    qf.setGroupByField( RoomReservation__c.Room__c );

    return this.executeAggregateQuery( qf, new Map<String, Object>{
        'roomIds'      => roomIds,
        'openStatuses' => DOM_RoomReservations.STATUSES_OPEN
    } );
}
// SELECT Room__c, COUNT( Id ) numOpenReservations
//  FROM  RoomReservation__c 
//  WHERE Room__c IN :roomIds AND Status__c IN :openStatuses
//  GROUP BY Room__c
[ {
  "result" : {
    "attributes" : {
      "type" : "AggregateResult"
    },
    "Room__c" : "a01Dz00000SSfALIA1",
    "numopenreservations" : 2
  }
} ]
DOM_Rooms.ValidateDeactivation.finalize
// Validate whether any open reservations for the deactivated rooms, bypass sharing for data correctness
List<dfflib_AggregateResult> numOpenReservationsPerRoom = SEL_RoomReservations
    .newInstance( dfflib_Database.Context.NONE ).selectOpenCountPerRoomId( deactivatedRoomIds );

Map<Id, Room__c> roomsById = new Map<Id, Room__c>( ( List<Room__c> ) state.currentList );
for( Integer i = 0, j = numOpenReservationsPerRoom.size(); i < j; i++ ){
    dfflib_AggregateResult result = numOpenReservationsPerRoom[ i ];
    Id roomId = ( Id ) result.get( 'Room__c' );
    if( Integer.valueOf( result.get( 'numOpenReservations' ) ) > 0 ){
        Room__c roomToSetError = roomsById.get( roomId );
        roomToSetError.addError( 'Cannot deactivate a room with open reservations.' );
    }
}
DOM_RoomsTest.validateDeactivation_withOpenReservations
// Shortened version focusing to mock dfflib_AggregateResult. Click GitHub icon on right for full code snippet.
// Create mocks
fflib_ApexMocks mocks = new fflib_ApexMocks(); 
dfflib_AggregateResult aggResult = ( dfflib_AggregateResult ) mocks.mock( dfflib_AggregateResult.class ); 
SEL_RoomReservations m_selReservations = ( SEL_RoomReservations ) dfflib.selector.defineMock( 
    mocks, SEL_RoomReservations.class, RoomReservation__c.SObjectType 
);

// Given - set data
Id roomId = fflib_IDGenerator.generate( Room__c.SObjectType );
Integer numOpenReservations = 3;

// Set mocks
mocks.startStubbing();
mocks.when( m_selReservations.selectOpenCountPerRoomId( expectedRoomIds ) )
    .thenReturn( new List<dfflib_AggregateResult>{ aggResult } );
mocks.when( aggResult.get( 'Room__c' ) ).thenReturn( roomId );
mocks.when( aggResult.get( 'numOpenReservations' ) ).thenReturn( numOpenReservations );
mocks.stopStubbing();

Example 2: SUM with a multi-field GROUP BY, HAVING filter and ordering by aggregate alias

Context: Imagine a WorkOrder which contains work for multiple parties, and should therefore be invoiced separately. Fixed callout costs should be invoiced to the Account which, by ratio, consumed the most work. Thus, the Account with the highest sum of duration over all WorkOrderLineItems should pay the costs. If the time spent is less than 10 minutes no fixed costs will be charged.

Requirement: This requires a SOQL query which groups by two fields (WorkOrderId and Paying_Account__c), filters to only receive Accounts having at least 10 minutes of work performed for that WorkOrder and sorts them negatively to have the Account with the highest sum on top, to avoid the need for looping.

public List<dfflib_AggregateResult> selectSumDurationPerPayingAccount( Set<Id> workOrderIds ){
    dfflib_QueryFactory qf = newQueryFactory( false )
        .selectFields( new List<Schema.SObjectField>{
            WorkOrderLineItem.WorkOrderId,
            WorkOrderLineItem.Paying_Account__c
        } )
        .selectFields( new Set<String>{ 'SUM( DurationInMinutes ) duration' } )
        .setCondition( 'WorkOrderId IN :workOrderIds' )
        .setGroupByField( 'WorkOrderId, Paying_Account__c' )
        .setHaving( 'SUM( DurationInMinutes ) > 10' )
        .setOrdering( 'SUM( DurationInMinutes )', dfflib_QueryFactory.SortOrder.DESCENDING );

    return this.executeAggregateQuery( qf, new Map<String, Object>{
        'workOrderIds' => workOrderIds
    } );
}
// SELECT WorkOrderId, Paying_Account__c, SUM( DurationInMinutes ) duration 
//   FROM WorkOrderLineItem 
//   GROUP BY WorkOrderId, Paying_Account__c 
//   HAVING SUM( DurationInMinutes ) > 10
//   ORDER BY duration DESC
[ {
    "result" : {
        "attributes" : {
        "type" : "AggregateResult"
        },
        "WorkOrderId" : "0WOP4000009eKFlOAM",
        "Paying_Account__c": "001Dz00000SSfALIA1",
        "duration" : 30
    }
  },
  {
    "result" : {
        "attributes" : {
        "type" : "AggregateResult"
        },
        "WorkOrderId" : "0WOP4000009eKFlOAM",
        "Paying_Account__c": "001Dz00000SSfALIA1",
        "duration" : 20
    }
  },
  {
    "result" : {
        "attributes" : {
        "type" : "AggregateResult"
        },
        "WorkOrderId" : "0WOP4000009bv3lOAA",
        "Paying_Account__c": "001Dz00002wI6C5IAK",
        "duration" : 42
    }
  }
]
// Flow Invocable method to get Paying Account with highest duration-sum per WorkOrder
List<dfflib_AggregateResult> results = SEL_WorkOrderLineItems
    .newInstance( dfflib_Database.Context.FULL).selectSumDurationPerPayingAccount( new Set<Id>( workOrderIds ) );

Map<Id, Decimal> maxSumDurationPerWO = new Map<Id, Decimal>();
Map<Id, Id> maxPayingAccountPerWO = new Map<Id, Id>();

for( Integer i = 0, j = results.size(); i < j; i++ ){
    dfflib_AggregateResult res = results[ i ];
    Id woId = ( Id ) res.get( 'WorkorderId' );
    Decimal currWOPASum = ( Decimal ) res.get( 'duration' );

    Decimal currMax = maxSumDurationPerWO.get( woId ) ?? 0;
    // Compare current max to next-Paying-Account aggregate sum
    // When current smaller, set the Paying Account as new 'highest'
    // When equal, no Paying Account should be returned
    if( currMax < currWOPASum ){
        maxPayingAccountPerWO.put( woId, ( Id ) res.get( 'Paying_Account__c' ) );
        maxSumDurationPerWO.put( woId, currWOPASum );
    } else if( currMax == currWOPASum ){
        maxPayingAccountPerWO.put( woId, null );
    }
}
@IsTest
private static void getPayingAccountWithHighestDurationSumPerWorkOrderId(){
    // create three mock-stubproviders to allow distinct method returns for identical method calls of aggregate results
    fflib_ApexMocks mocks  = new fflib_ApexMocks();
    fflib_ApexMocks mocks2 = new fflib_ApexMocks();
    fflib_ApexMocks mocks3 = new fflib_ApexMocks();
    SEL_WorkOrderLineItems m_selWOLIs  = ( SEL_WorkOrderLineItems ) dfflib.selector.defineMock( mocks, SEL_WorkOrderLineItems.class, WorkOrderLineItem.SObjectType );

    // Given - set data (2 workorders, 2 paying accounts, 3 aggregate results)
    dfflib_AggregateResult aggResultWO1  = ( dfflib_AggregateResult ) mocks.mock( dfflib_AggregateResult.class );
    dfflib_AggregateResult aggResultWO2a = ( dfflib_AggregateResult ) mocks2.mock( dfflib_AggregateResult.class );
    dfflib_AggregateResult aggResultWO2b = ( dfflib_AggregateResult ) mocks3.mock( dfflib_AggregateResult.class );
    Id wo1 = fflib_IDGenerator.generate( WorkOrder.SObjectType );
    Id wo2 = fflib_IDGenerator.generate( WorkOrder.SObjectType );
    Id pa1 = fflib_IDGenerator.generate( Account.SObjectType );
    Id pa2 = fflib_IDGenerator.generate( Account.SObjectType );
    List<Id> inputIds = new List<Id>{ wo1, wo2 };    

    // set mocks
    mocks.startStubbing();
    mocks.when( m_selWOLIs.selectSumDurationPerPayingAccount( ( Set<Id> ) fflib_Match.anyObject() ) )
        .thenReturn( new List<dfflib_AggregateResult>{ aggResultWO1, aggResultWO2a, aggResultWO2b } );
    mocks.when( aggResultWO1.get( 'WorkorderId' ) ).thenReturn( wo1 );
    mocks.when( aggResultWO1.get( 'Paying_Account__c' ) ).thenReturn( pa1 );
    mocks.when( aggResultWO1.get( 'duration' ) ).thenReturn( 5.5 );
    mocks.stopStubbing();

    mocks2.startStubbing();
    mocks2.when( aggResultWO2a.get( 'WorkorderId' ) ).thenReturn( wo2 );
    mocks2.when( aggResultWO2a.get( 'Paying_Account__c' ) ).thenReturn( pa1 );
    mocks2.when( aggResultWO2a.get( 'duration' ) ).thenReturn( 10 );
    mocks2.stopStubbing();

    mocks3.startStubbing();
    mocks3.when( aggResultWO2b.get( 'WorkorderId' ) ).thenReturn( wo2 );
    mocks3.when( aggResultWO2b.get( 'Paying_Account__c' ) ).thenReturn( pa2 );
    mocks3.when( aggResultWO2b.get( 'duration' ) ).thenReturn( 10 );
    mocks3.stopStubbing();

    // When - perform test
    List<Id> payingAccountIds = Flow_GetAccountWithHighestDuration.getPayingAccountWithHighestDurationSumPerWorkOrderId( inputIds );

    // Then - validate wo1 had highest and wo2 as null as equal value
    System.Assert.areEqual( inputIds.size(), payingAccountIds.size(), 'Expected same number response as input' );
    System.Assert.areEqual( pa1, payingAccountIds[ 0 ], 'Expected first WO Id to return PA1 as only one, being the highest' );
    System.Assert.areEqual( null, payingAccountIds[ 1 ], 'Expected second WO Id to return null, as two Paying Accounts have same sum' );
}

Advanced: challenge on stubbing multiple aggregate results

fflib_ApexMocks ties each stubbed return to its own mock instance (this). Thus, a sequential stub on the same mocked instance (Domain, Selector, AggregateResult) will overwrite earlier stubs on that instance.

Hence, when the same method (.get( 'duration' )) must return a different value for each dfflib_AggregateResult instance, each instance needs its own fflib_ApexMocks instance. In the 'unit test' above, three instances and stub-blocks make it possible to imitate three separate dfflib_AggregateResult database row responses.

It is essential to ensure the fflib_ApexMocks instance passed into the defineMock(), matches the fflib_ApexMocks instance used to stub the dfflib_AggregateResult instance.

Above example shows the ability to query SObject related aggregates, specifying the fields to query, group, filter and order upon. It's important that all fields referenced are also included in the grouping to avoid malformed query errors.

The response List<dfflib_AggregateResult> contains the exact fields and aliases requested. Those can be simply 'looped' and should be cast to the expected datatype as the aggregate result is context independent.

Note, the usage of setOrdering() which clears and sets. Though addOrdering would have worked as well, due to the default clearing/emptyOrdering() when calling setGroupByField().

When no alias is set (like duration), Salesforce will return expr0, expr1, .... Note, Salesforce doesn't allow aliases to be used within the query, like HAVING duration or ORDER BY duration

Due to the builder structure of the QueryFactory, it's possible to 'chain' the methods. In which the sequence of method-calls has no effect for the resulting query.


Cursor and Pagination Cursor

In Spring '26 Database.Cursor became generally available, which allows cursor-based navigation through large result sets without the 2,000-record ceiling that OFFSET imposes. This is particularly useful for UIs that need to stream through thousands of records efficiently.

The structure of the selector method remains the same, only the final execution method should be this.getCursor() or this.getPaginationCursor(), providing the same parameters as this.executeQuery() (dfflib_QueryFactory and optional bind-variable map).

// Instead of executeQuery(), call getCursor() or getPaginationCursor() for cursor-based navigation
return this.getCursor( qf, bindVars );              // Database.Cursor
return this.getPaginationCursor( qf, bindVars );    // Database.PaginationCursor

The framework automatically validates whether no GROUP BY or FOR UPDATE is set, or will throw an InvalidCursorQueryException as these are incompatible with Cursors.


FOR VIEW, REFERENCE and UPDATE

Salesforce has three SOQL FOR clauses that control record-tracking and locking behaviour:

SOQL CommandDescription/use-case
FOR VIEWRetrieve records for read-only display (UI views); use when you only need to show data without modifying it.
FOR REFERENCERetrieve records to use as references (e.g., populate lookups or obtain Ids); use when you need stable identifiers or reference data.
FOR UPDATELock returned rows for the duration of the transaction to prevent concurrent modifications; use before performing DML to avoid race conditions.

To support developers to leverage dfflib_QueryFactory when in need for these methods, one can append via below methods:

qf.setForView( true );      // marks retrieved records as recently viewed by the running user
qf.setForReference( true ); // marks retrieved records as recently referenced
qf.setForUpdate( true );    // row-locks retrieved records for the duration of the transaction

Usage considerations

  • FOR UPDATE is incompatible with ORDER BY. Call emptyOrdering() to prevent a runtime exception by Salesforce
  • When both FOR VIEW and FOR REFERENCE are set, an InvalidImplementationException is thrown as those are mutually exclusive

Location based queries

To enable location-based SOQL queries, SOQL has a DISTANCE( field, GEOLOCATION( latitude, longitude ), unit ) method which calculates the distance, in kilometres ('km') or miles ('mi'), from each record's location to the specified geolocation.

This can be applied in the SELECT, WHERE and ORDER BY sections, and can be set via the standard methods, passing a String, as shown below.

The getDistanceString( SObjectField, DistanceUnit ) was introduced to take away overhead from developers about finding the exact signature, parameter sequence, but also the restriction of unit not being allowed as bind-variable, and the cumbersome mapping towards 'km' and 'mi'. This method performs the getFieldPath and simply returns the DISTANCE expression via an easy to (re-)use method. Consider assigning the DISTANCE expression to a variable when reusing across SOQL sections.

SEL_Rooms.selectNearestRooms
public List<Room__c> selectNearestRooms( Decimal latitude, Decimal longitude, Decimal radiusKm ){
    dfflib_QueryFactory qf = newQueryFactory( false );
    String distanceString = qf.getDistanceString( Room__c.Location__c, dfflib_QueryFactory.DistanceUnit.KILOMETERS );

    qf.selectField( distanceString );
    qf.setCondition( distanceString + '< :radiusKm' );
    qf.setOrdering( distanceString, dfflib_QueryFactory.SortOrder.ASCENDING, true );

    return this.executeQuery( qf, new Map<String, Object>{
        'latitude'  => latitude,
        'longitude' => longitude,
        'radiusKm'  => radiusKm
    } );
}
// SELECT DISTANCE( location__c, GEOLOCATION(:latitude, :longitude), 'km' ) 
//   FROM Room__c 
//   WHERE DISTANCE( Location__c, GEOLOCATION(:latitude, :longitude), 'km' ) < :radiusKm 
//   ORDER BY DISTANCE( Location__c, GEOLOCATION(:latitude, :longitude), 'km' ) ASC NULLS LAST
  • The DistanceUnit enum includes KILOMETERS and MILES .
  • Distance-conditions only accept < or > operators, =, <= and >= aren't allowed.

Pagination (LIMIT, OFFSET)

  • LIMIT is used to restrict the number of records retrieved and required for queries with FIELDS(ALL), FIELDS(CUSTOM) or applying OFFSET. This is mostly relevant to manage performance or avoid retrieving unnecessary data.
  • OFFSET allows you to skip a specified number of records, till a limit of 2,000 records, making it useful for (smaller) pagination or batch-processing. For larger data sets, use getQueryLocator() (Batch Apex) or getCursor() instead.
qf.setLimit( 50 );
qf.setOffset( 100 );

Ensure to have a strict ORDER BY so the data scope remains the same.


Polymorphic fields (TYPEOF)

To query parent-fields from a polymorphic field SOQL provides:

TYPEOF [FIELD] 
    WHEN [OBJECT] THEN [FIELDS] 
    WHEN [OBJECT2] THEN [FIELDS2] 
    ELSE [FIELDS3] 
END

This way lookup fields like Owner (can be a Group or User) or WhoId (can be a Contact or Lead) can be specifically queried.

Developers can leverage selectFieldsTypeOf() , passing the field name and a map of SObjectType to Set<Schema.SObjectField> to define what to retrieve per SObjectType.

public List<Task> selectWhoInfo( Set<Id> taskIds ){
    dfflib_QueryFactory qf = newQueryFactory( false );
    qf.selectFieldsTypeOf( 'Who', new Map<Schema.SObjectType, Set<Schema.SObjectField>>{
        Contact.SObjectType  => new Set<Schema.SObjectField>{ Contact.FirstName, Contact.LastName, Contact.Email },
        Lead.SObjectType => new Set<Schema.SObjectField>{ Lead.FirstName, Lead.LastName, Lead.Company, Lead.Email }
    } );
    qf.setCondition( 'Id IN :taskIds' );
    qf.setOrdering( Task.ActivityDate, dfflib_QueryFactory.SortOrder.ASCENDING );

    return this.executeQuery( qf, new Map<String, Object>{
        'taskIds' => taskIds
    } );
}
// SELECT TYPEOF Who 
//          WHEN Contact THEN FirstName, LastName, Email 
//          WHEN Lead THEN FirstName, LastName, Company, Email
//        END
//  FROM  Task
//  WHERE Id IN :taskIds
//  ORDER BY ActivityDate ASC NULLS FIRST
List<Task> tasksList = SEL_Tasks.newInstance( dfflib_Database.Context.FULL ).selectWhoInfo( taskIdsSet );
for( Integer i = 0, j = tasksList.size(); i < j; i++ ){
    Task t = tasksList[ i ];
    if( t.Who instanceof Contact ){
        Contact c = ( Contact ) t.Who;
        // Perform magic
    } else if( t.Who instanceof Lead ){
        Lead l = ( Lead ) t.Who;
        // Perform magic
    }
}

Note, TYPEOF is only valid for polymorphic lookup fields and can't be combined with GROUP BY.


Testing a Selector

The responsibilities of selectors are to construct a SOQL query and call the database with the correct variables. This implies a unit test method should assert exactly those two responsibilities. Implying there is no need to validate whether the database indeed returned the correct data, as that concern is separated to the Database Proxy and standard Salesforce.

Assertion methods

Effectively this means mocking the dfflib_Database_IQuery and performing a mocks.verify() on the query() method to capture the SOQL string and optional bind-variables. For ease of use, centralisation and deduplication of code, D'fflib provides below set of methods to 1) mock the proxy, 2) verify query execution and 3) validate constructed query and bind-variables .

1. Mock the database proxy

MethodWhat it does
mockExecuteQuery()Mocks the database proxy for standard queries; returns the fflib_ApexMocks instance
mockExecuteAggregateQuery()Same as above, but also pre-stubs an empty List<AggregateResult> return so the internal cast to List<dfflib_AggregateResult> succeeds

2. Verify the query execution method was properly called

After the selector execution ('WHEN'), the verify*()-methods assert whether the query was executed. Optionally comparing the number of variables and bind-variables provided. Returning inner-class SelectorVerificationResults for method-specific validation:

MethodVerifies
verifyQuery( mocks )executeQuery() was called without a bind-variable map
verifyQueryAndBindVariableKeys( mocks )executeQuery() was called with a bind-variable map, and every :bindVar in the query string has a matching key in the map
verifyQueryLocator( mocks )Same as verifyQuery() but for getQueryLocator() calls
verifyQueryLocatorAndBindVariableKeys( mocks )Same as verifyQueryAndBindVariableKeys() but for getQueryLocator() calls
verifyCursor( mocks )Same as verifyQuery() but for getCursor() calls
verifyCursorAndBindVariableKeys( mocks )Same as verifyQueryAndBindVariableKeys() but for getCursor() calls
verifyPaginationCursor( mocks )Same as verifyQuery() but for getPaginationCursor() calls
verifyPaginationCursorAndBindVariableKeys( mocks )Same as verifyQueryAndBindVariableKeys() but for getPaginationCursor() calls

3. Validate specific SOQL parts via SelectorVerificationResults methods and variables

public class SelectorVerificationResults {
    dfflib_QueryFactory qf;                  // The QueryFactory instance used to construct the query
    String query;                           // The SOQL query string injected into the database proxy
    Map<String, Object> map_bindVariables;  // The bindvariables map injected into the database proxy
}
MethodWhat it asserts
.validateTable( SObjectType )FROM clause matches the expected SObject type
.validateFields( Set<SObjectField|String> )All specified fields are present in the SELECT clause
.validateCondition( String )WHERE clause exactly matches the expected condition (case-insensitive)
.validateCondition( String, true )WHERE clause contains the expected condition (partial match)
.validateBindVariableValue( key, value )A specific bind variable in the map equals the expected value

Example

SEL_RoomReservationsTest.selectByRoomId
@IsTest
private static void selectByRoomId(){
    // create mocks to prevent database calls
    SEL_RoomReservations selector = SEL_RoomReservations.newInstance( dfflib_Database.Context.FULL );
    fflib_ApexMocks mocks = selector.mockExecuteQuery();

    // Given - setup Set to validate bind-passing
    Set<Id> roomIds = new Set<Id>();

    // When - perform test
    selector.selectByRoomId( roomIds );

    // Then - validate query, condition and fields
    selector.verifyQueryAndBindVariableKeys( mocks )
        .validateCondition( 'Room__c IN :roomIds' )
        .validateBindVariableValue( 'roomIds', roomIds )
        .validateFields( new Set<SObjectField>{ RoomReservation__c.Room__c, RoomReservation__c.Status__c } );
}
SEL_RoomReservationsTest.selectOpenCountPerRoomId
@IsTest
private static void selectOpenCountPerRoomId(){
    // create mocks to prevent database calls - use mockExecuteAggregateQuery for aggregate queries
    SEL_RoomReservations selector = SEL_RoomReservations.newInstance( dfflib_Database.Context.FULL );
    fflib_ApexMocks mocks = selector.mockExecuteAggregateQuery();

    // Given - setup Set to validate bind-passing
    Set<Id> roomIds = new Set<Id>();

    // When - perform test
    selector.selectOpenCountPerRoomId( roomIds );

    // Then - validate query, condition and fields (use Set<String> for aggregate fields)
    dfflib_QueryFactory qf = selector.verifyQueryAndBindVariableKeys( mocks )
        .validateFields( new Set<String>{
            'Room__c',
            'COUNT( Id ) numOpenReservations'
        } )
        .validateCondition( 'Room__c IN :roomIds AND Status__c IN :openStatuses' )
        .validateBindVariableValue( 'roomIds', roomIds )
        .validateBindVariableValue( 'openStatuses', DOM_RoomReservations.STATUSES_OPEN )
        .qf;
    System.Assert.areEqual( 'Room__c', qf.getGroupByField(), 'Expected Group by on Room__c' );
}

Unlocking of proper selector unit test asserts

Before the introduction of the Database Proxy, thus when having Database.query() as last statement in the selector method, it was not possible to properly assert the constructed query, nor the bind variables passed to it. Because of this asserts were often a 'formality' and not really adding any value, other than covering lines.

Often developers chose to:

  • Create data and validate a proper return. Causing longer unit-test runtimes.
  • Use Limits.getQueries() to assert the number of queries performed. Only validating the execution, but not the query.
  • Track the @TestVisible lastQueryFactory in dfflib_QueryFactory which is set upon toSOQL() to allow assertions.

This implies developers (and reviewers) should become conscious of properly asserting the selector method's responsibilities.


Migration steps

For existing code to leverage D'fflib features, a couple migration steps are required. Below steps apply once D'fflib extensions are installed (Getting Started), thus e.g. already having the override added in all existing Selectors.

Class changes

  1. Ensure Dfflib_Selector_Config__mdt record exists
  2. Change extends fflib_SObjectSelector to extends dfflib_SObjectSelector
  3. Change [Application].selector.newInstance() to dfflib.selector.newInstance()
  4. Create an overload or update the existing .selector.newInstance() to receive a dfflib_Database.Context. Recommendation is to construct this dynamically via an input-parameter, but this requires all calling-classes to pass the applicable dfflib_Database.Context. Another option is to use a default context, set in the newInstance method of the Selector.
    public static SEL_Assets newInstance( dfflib_Database.Context ctx ){
        return ( SEL_Assets ) dfflib.selector.newInstance( Asset.SObjectType, ctx );
    }
    public static SEL_Assets newInstance(){
        return ( SEL_Assets ) dfflib.selector.newInstance( Asset.SObjectType, dfflib_Database.Context.FULL );
    }
    When dynamic, find and replace SEL_[SObjects].newInstance() calls to include the dfflib_Database.Context.
  5. Validate sharing annotation of Selector class and relevancy/reasoning
    Best practice is with sharing (still used e.g. when calling SObjectDescribe methods)

Method changes

  1. Simplify newQueryFactory-call, by removing enforce-Booleans
  2. Change fflib_QueryFactory to dfflib_QueryFactory
  3. Replace Database.query by this.executeQuery()
    or the equivalent for Cursor, QueryLocator, or aggregate queries
  4. Pass bind variables via a Map
    Decommission need for local 'copies' of static variables (like String recordType = DOM_Accounts.RECTYPE_BUSINESS) as this can now directly be set to the Map
  5. Remove without sharing inner-classes and withSharing Boolean parameters

Test class changes

  1. Omit any test data creation or Limits.getQueryRows()
  2. Mock the query to prevent execution via mockExecuteQuery() (or mockExecuteAggregateQuery())
  3. Add strict assert to validate proper query construction leveraging provided Assertion methods

Casing of SOQL fields

When using enforceFLS fields are validated and deduplicated by SObjectDescribe. With the migration to FLSEnforcement and dfflib_Database.Context the field-validation became server-side, while the deduplication is still desired to prevent SOQL errors.

Therefore the QueryFactory automatically converts fields to lowercase to allow deduplication. For manual asserts it's therefore important to validate against lowercase fields. validateFields() automatically converts to lowercase. Legacy constructs with enforceFLS keep their input casing.


Except for the listed items, the content on this site is licensed under CC-BY-NC-4.0
and allows sharing, provided an appropriate credit and link to source.

On this page