This is the next post in a series of posts covering how to use FFLIB in your project. In this post we will be covering the selector layer, the part of the application which handles all of your queries. Last time we covered a basic introduction to the domain layer.
Why do we need a selector layer?
A very common problem when working with medium to large projects on Salesforce is that there can be the issue of duplicating queries across the system and or even the dreaded field was not selected exception.
Having a selector layer or a class which contains all of the queries for a given object encourages reuse of the queries across the whole application, whilst also reducing the risk of exceptions being thrown due to fields not being selected as we will be using common queries.
Security is also another important reason for keeping the queries in a central location as it becomes must easier to maintain and prevent SOQL/SOSL injections. Additionally, checks can be done within this layer to ensure that the current user has field level access to fields and also whether they are allowed to read data from a given sObject. These checks are much easier to perform in a central location and prevent unauthorised data exposure in our applications.
The selector layer offered by FFLIB brings things together and offers:
- Centralising our 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.
A small naming convention
The selector layer can be called from anywhere in the application, such as the domain, service and or even another selector class. As such it’s important before we continue is to highlight that it’s best naming your selector classes plural as this will help you and other developers in your team ensure that all of your queries are designed for bulk instead of returning singular rows.
Creating our selector
We’re going to create a selector for the Opportunity sObject and we’re going to follow the naming convention as mentioned above and call it OpportunitiesSelector.
The next step we need to do is to extend the class fflib_SObjectSelector. This will allow us to define the current as a selector and to also inherit a lot of functionality specific to this layer. When we extend the super class we also have to add two mandatory methods; one for indicating which sObject the selector is for and the other being which fields should be selected from current sObject.
public class OpportunitiesSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Opportunity.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Opportunity.AccountId, Opportunity.Id, Opportunity.Name, Opportunity.StageName }; } }
At this point we’ve created a fully functional, but not very useful selector. We can now start to add our queries which we want to use across our application to it. Add the following method to your class:
public List<Opportunity> selectById(Set<Id> recordIds){ return (List<Opportunity>) selectSObjectsById(recordIds); }
Selecting records using their record ID’s is a very common query to create in our applications, so out of the box there is a utility method to aid doing this.
When we use the new selectById method in our application then the following query is built using the sObject we defined, common fields we have defined and it sets up the where clause for us.
SELECT AccountId, Id, Name, StageName FROM Opportunity WHERE Id IN :idSet
Batch jobs
You can also use selectors for batch jobs as well, however typically they require query locators instead of lists of sObjects to work with.
In order to set up query locators to use the sObject we defined, common fields and our where conditions, we need to use the query factory. This factory is going to construct the SOQL query as a string which can then be passed into our query locator.
Below is an example on how to create a query locator which will scan over all of the opportunities in the database.
public Database.QueryLocator queryLocatorAllOpportunities(){ return Database.getQueryLocator( newQueryFactory().toSOQL() ); }
To help convey what the framework is doing, below is how we would have to write the method without using the query factory.
public Database.QueryLocator queryLocatorAllOpportunities(){ return Database.getQueryLocator( 'SELECT AccountId, Id, Name, StageName FROM Opportunity' ); }
Selecting fields through relationships
This is one of the most useful features SOQL, the ability to select fields from other sObjects through relationship (lookup) fields without the need to write joins. So far we’ve only covered how to build queries with only fields defined for the current sObject. Now we’re going to cover how we can select fields through relationship fields.
The framework offers two different ways of achieving this. Create a new query factory…
- …and add the field path.
- …and combine it with another selector.
Adding field paths
Adding a field path to a query factory is relatively easy and you can start selecting fields through relationships fairly quickly. In the example below we select not only the associated accounts name, but also the grandparent account name too.
public List<Opportunity> selectWithTwoParentAccounts(){ return Database.query( newQueryFactory() .selectField('Account.Name') .selectField('Account.Parent.Name') .toSOQL() ); }
The above code will generate a query similar to the one below:
SELECT Account.Name, Account.Parent.Name, StageName, ... FROM Opportunity
Using another selector
Combining selectors together is really useful when you need to ensure you have a common set of fields selected no matter how or where the source sObject was.
An example maybe you have an account which is related to a custom object called Address__c. Sometimes we need to query the address object directly and sometimes we query the object through a relationship field on the account. In both cases we may have functionality in our application which works with addresses and expects the same fields to be loaded.
In the case of the example we could solve the issue by ensuring our selectors for both the account and address sObjects always use the same fields. However, this is where errors and creep into our application as sometimes we forget or don’t even know to add the fields elsewhere.
Instead, we can define all of the fields in a central place, this being the address selector. When we want to select fields from the address object from the account selector we can combine the two together and the query factory will do the rest.
Let’s get started by creating our addresses selector class.
public class AddressesSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Address__c.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Address__c.Street__c, Address__c.City__c, Address__c.PostalCode__c }; } public List<Address__c> selectById(Set<Id> recordIds){ return (List<Address__c>) selectSObjectsById(recordIds); } }
At this point we’ve centralised the fields which we need to select always in the addresses selector. Now we can build our accounts selector and incorporate the addresses selector.
public class AccountsSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Account.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Account.Id, Account.Name }; } public List<Account> selectById(Set<Id> recordIds){ fflib_QueryFactory query = newQueryFactory(); fflib_SObjectSelector addressesSelector = new AddressesSelector(); addressesSelector.configureQueryFactoryFields(query, 'InvoiceAddress__r'); return (List<Account>) Database.query( query.toSOQL() ); } }
In the above example we are creating a new instance of the addresses selector and then we are configuring the query factory within it by passing in our query factory for the account and asking it to be merged together as one.
The output of the built query would look like this:
SELECT Id, Name, InvoiceAddress__r.Street__c, InvoiceAddress__r.City__c, InvoiceAddress__r.PostalCode__c FROM Account
The where clause
Most queries which we need to create in our application require where clauses and FFLIB exposes a very simple interface for this. You just provide a string. That’s it.
public class AccountsSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Account.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Account.Id, Account.Name }; } public List<Account> selectByName(Set<String> names){ fflib_QueryFactory query = newQueryFactory(); query.setCondition('Name IN :names'); return (List<Account>) Database.query( query.toSOQL() ); } }
Be aware that in the where condition there are no security checks performed done by the framework to prevent SOQL injections. Ensure that any user supplied input is correctly escaped using String.escapeSingleQuotes.
Ordering
There are two ways you can influence the ordering of within your SOQL queries:
- Use a default ordering
- Apply the ordering using a query factory
Default ordering
Default ordering is whereby all queries which do not explicitly define their own ordering through setting up a query using a query factory will inherit the default ordering.
public class AccountsSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Account.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Account.Id, Account.Name }; } public List<Account> selectById(Set<Id> recordIds){ return (List<Account>) selectSObjectsById(recordIds); } public override String getOrderBy(){ return 'Name DESC'; } }
The selectById method will use a query similar to the one below:
SELECT Id, Name FROM Account WHERE Id IN :idSet ORDER BY Name DESC
Using the query factory
You can also define the ordering to be applied in your queries using the query factory. Below you can see the method signatures within the query factory which you can use to add ordering.
// addOrdering(String fieldName, SortOrder direction, Boolean nullsLast) // addOrdering(SObjectField field, SortOrder direction, Boolean nullsLast) // addOrdering(String fieldName, SortOrder direction) // addOrdering(SObjectField field, SortOrder direction)
Below is an example how to add ordering to your queries using the query factory.
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() ); }
Limiting the results
To limit your results you will need to use again the query factory from the selector layer. In the example below you can see we are limiting the results to 100 rows.
public class AccountsSelector extends fflib_SObjectSelector { ... public List<Account> selectByName(Set<String> names){ fflib_QueryFactory query = newQueryFactory(); query.setLimit( 100 ); return (List<Account>) Database.query( query.toSOQL() ); } }
Subqueries
Adding subqueries into your queries is also possible and is again by joining together multiple selectors. By joining selectors together we again are reusing queries and ensured that we are always selecting the same common set of fields. Although, as you will see in a moment we can also control which fields are added to the subquery.
There are four different ways of adding subqueries, although they are all very similar and only have slight differences in behaviour.
Automatic relationship lookup with selector fields included
When adding a subquery you will always need to specify the relationship to select from. The query factory offers a shortcut to determine the relationship field so you do not need to worry about looking this up or even if it changes. This approach should only be used though when the child sObject is the only type beneath the parent sObject. Also, not all standard sObjects in Salesforce have the child relationship defined or available in the API for the framework to inspect. Most of the time this approach will work fine, but just be aware of the limitations.
To demonstrate how to add a subquery using the query factory we are going to set up a query which will return opportunities which match on their ID’s, with their opportunity line items.
First, create the opportunity line items selector and call it OpportunityLineItemsSelector.
public class OpportunityLineItemsSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return OpportunityLineItem.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { OpportunityLineItem.Id, OpportunityLineItem.Quantity, OpportunityLineItem.SalesPrice }; } }
Now we can create our opportunity selector and include the subquery to load the opportunity line items.
public class OpportunitiesSelector extends fflib_SObjectSelector { public Schema.SObjectType getSObjectType(){ return Opportunity.sObjectType; } public override List<Schema.SObjectField> getSObjectFieldList(){ return new List<Schema.SObjectField> { Opportunity.Id, Opportunity.StageName }; } public List<Opportunity> selectByIdWithLineItems(Set<Id> recordIds){ fflib_QueryFactory query = newQueryFactory(); query.setCondition('Id IN :recordIds'); new OpportunityLineItemsSelector(). addQueryFactorySubselect(query,'OpportunityLineItems'); return (List<Opportunity>) Database.query( query.toSOQL() ); } }
Special thanks to the following developers over at Stackexchange noticing an original mistake in the previous example shown. https://salesforce.stackexchange.com/questions/191492/salesforce-selector-layer-subquery
In the above code we are adding in a query factory to handle the building of the subquery into the opportunity query factory. Behind the scenes the query factory is working out the relationship name (OpportunityLineItems) and is setting up the query accordingly.
This will produce a SOQL query similar to the one below:
SELECT Id, StageName, (SELECT Id, Quantity, SalesPrice FROM OpportunityLineItems) FROM Opportunity WHERE Id IN :idSet
As you can see in the above produced query, all of the fields were selected from the opportunity line items selector automatically as well. This is a useful behaviour to ensure we are always selecting the same data everywhere in our application.
Selecting fields with field sets
Another option to select fields in the query factory is the ability to select using field sets. Field sets are a list of field paths which relate to a given sObject. A field path is either only the API name of a given field or the full path to the field through relation fields (e.g. Account.Name or Account.InvoiceAddress__r.Name).
public class OpportunitiesSelector extends fflib_SObjectSelector { ... public List<Opportunity> selectByIdWithLineItems(Set<Id> recordIds){ fflib_QueryFactory query = newQueryFactory(); query.setCondition('Id IN :recordIds'); query.selectFieldSet( Opportunity.fieldsets.MyFieldset ); return (List<Opportunity>) Database.query( query.toSOQL() ); } }
Security
The security checks built into the selector layer make your life easier as a developer as you will not need to worry about enforcing these checks. They are done by the framework as the construct the query.
These checks cover ensuring that the user has read access to the sObject it is attempting to select from and also ensuring all of the fields returned as accessible to the current user.
At times you may need to have fine grained control how these security checks are done to suit your application needs. These security checks are performed within the query factory and as such when we request a new instance of one we need to instruct it to disable these checks if not required.
Below you will see a new overloaded newQueryFactory method which accepts three parameters.
- assertCRUD – set to true if you wish to assert that the user has at least read access.
- enforceFLS – set to true if you wish to assert that the user can read from all of the fields selected.
- includeSelectorFields – not security related, controls whether to include the selector fields.
public class OpportunitiesSelector extends fflib_SObjectSelector { ... public List<Opportunity> selectByIdWithLineItems(Set<Id> recordIds){ // newQueryFactory(Boolean assertCRUD, Boolean enforceFLS, Boolean includeSelectorFields) fflib_QueryFactory query = newQueryFactory(false, false, true); query.setCondition('Id IN :recordIds'); return (List<Opportunity>) Database.query( query.toSOQL() ); } }
Wrapping up
In future posts I’ll cover how to unit test this layer and how it should be used within the other layers in the enterprise design patterns. I would definitely recommend taking a look at the source code to gain a greater understanding of how the selector works.
September 8, 2017 at 9:27 pm
Thank you Adam for wonderful post. I was trying to set it up, but got compile error. Can you please take a look at this stackexchange link https://salesforce.stackexchange.com/questions/191492/salesforce-selector-layer-subquery ?
LikeLike
November 30, 2017 at 6:42 pm
Hello Mitesh, sorry for the late response to you on this. Indeed there was an error within the post and I’ve updated the post and linked to the stackexchange question for future reference.
LikeLike
March 31, 2019 at 8:00 pm
Relationship fields query is generating fine but the result doesn’t fetch the relationship field results.
LikeLike
March 31, 2019 at 8:05 pm
public List selectById(Set leadIds) {
fflib_QueryFactory query = newQueryFactory();
fflib_SObjectSelector contactSelector = new FNS_ContactSelector();
contactSelector.configureQueryFactoryFields(query, ‘FinServ__ReferredByContact__r’);
List leadList = Database.query( query.toSOQL());
System.debug(‘leadList::’+leadList);
return (List) Database.query( query.toSOQL() );
}
Query:
SELECT CurrencyIsoCode, FinServ__ReferredByContact__r.CurrencyIsoCode, FinServ__ReferredByContact__r.Id, FinServ__ReferredByContact__r.Name, Id, Name FROM Lead ORDER BY Name ASC NULLS FIRST
leadList:
(Lead:{CurrencyIsoCode=USD, FinServ__ReferredByContact__c=0031U00000Gxve0QAB, Id=00Q1U000006DzPvUAK, Name=Beige Brown, RecordTypeId=0121U000000MwmbQAC}, Lead:{CurrencyIsoCode=USD, Id=00Q1U000006DkrpUAC, Name=Jim J, RecordTypeId=0121U000000MwmcQAC}, Lead:{CurrencyIsoCode=USD, Id=00Q1U000005R9lLUAS, Name=John Gardner}, Lead:{CurrencyIsoCode=USD, Id=00Q1U000005R9lKUAS, Name=Sarah Loehr})
What’s missing:
FinServ__ReferredByContact__r.Id, FinServ__ReferredByContact__r.Name
What did i do:
I created contact selector just like your address selector.
Lead has look up to contact object
But the result of contact after executing is not showing the result for relationship fields.
LikeLike
April 3, 2020 at 12:00 am
Do you have a post on how to create unit tests for the selector layer yet?
LikeLike
July 14, 2020 at 3:28 pm
Hello Trevor, I think there are few different approaches to take when it comes to testing selectors.
– Insert test data, run the selector, and verify the test result.
– Make a modification within FFLIB to capture the generated SOQL query and verify it matches what you expect.
Inserting and verifying the result from the query is the best way to verify the selector layer, and is especially critical for methods which are sensitive for exposing incorrect data (sharing model related). You can use the System.runAs method to further test different users access levels.
However, as your system grows this added complexity and inserting of test data doesn’t scale very well. Depending on your test data factory, it can become difficult to maintain unit tests for hundreds of selectors. And, it can also impact the overall deployment times due to how long it takes to create the test data for each selector.
Personally, I like the balance of the two approaches. Verifying the that the SOQL query was generated correctly for non-critical parts of the system in the aim for speed, but for more critical areas such as objects holding sensitive data, create test data and verify the result.
Let me know if you need more information or what your thoughts are on this.
LikeLike