FFLib Selectors with SOQL For Loops

Question

I'm considering implementation of FFLib, or at least parts of it, throughout our enterprise. Right now I'm focusing on the Selector and Query classes to consolidate our SOQL into a Selector Layer. I'm having a hard time finding examples/discussions surrounding the Selector Class and how it would work (or wouldn't?) with SOQL for loops.

in the AccountsSelector of the fflib-apex-common-samplecode, AccountsSelector.selectById(idSet) casts the return value of SObjectSelector.selectSObjectsById(idSet) to a List<Account>, and then returns the List to the caller.

public List<Account> selectById(Set<Id> idSet){
  return (List<Account>) selectSObjectsById(idSet);
}

(For reference, the SObjectSelector.selectSObjectsById method looks like this):

public virtual List<SObject> selectSObjectsById(Set<Id> idSet){
  return Database.query(buildQuerySObjectById());
}

So in the context of an AccountsService class, we might do:

List<Account> listAccountsById = AccountsSelector.newInstance().selectById(accountIds);

This works great as long as we aren't dealing with a lot of records or heap concerns. If we are, then we would leverage SOQL For Loops, as outlined in the Apex Developer Guide (emphasis mine):

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. While the standard queries discussed in SOQL and SOSL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API. Developers should always use a SOQL for loop to process query results that return many records, to avoid the limit on heap size.

So, since the examples in the Selector layer of the fflib seem to focus on returning a List to the caller, I'm not clear on what the correct syntax / implementation is for using the Selector layer with SOQL For Loops. We of course want to maintain Separation of Concerns by consolidating all of the query logic into the Selector class, but then how do we get the benefit of leveraging the built-in performance benefits of SOQL for loops?

I've seen a few things online that say we should build the query separately using the QueryFactory and then use .toSOQL to return the string to Database.query in the for loop, something like...

fflib_QueryFactory query = new fflib_QueryFactory(Account.sObjectType);
query.selectField('name').selectField('Id').setCondition('Id In :accountIds');

for(Account objAccount : database.query(query.toSOQL()){
  // Do Stuff
}

This of course breaks the separation of concerns principle by having query code inside of the Service layer. How do we properly use the fflib Selector layer design with SOQL for loops?

Answer 1

We can't use custom Iterator in a for each loop, which would be necessary in order for us to leverage the QueryLocator for loop pattern. So, given we can't do this, we would need to change the implementation to make this work, something like this:

public virtual List<SObject> selectSObjectsById(Set<Id> idSet){
  return Database.getQueryLocator(buildQuerySObjectById());
}
public Database.QueryLocator selectById(Set<Id> idSet){
  return selectSObjectsById(idSet);
}

And then in your code, you would:

Iterable<Account> queryLocator = (Iterable<Account>)AccountsSelector.newInstance().selectById(accountIds);
Iterator<Account> it = queryLocator.iterator();
while(it.hasNext()) {
  Account record = it.next();
  // Do something with record
}

However, attempting to do currently results in:

An internal server error has occurred

An error has occurred while processing your request. The salesforce.com support team has been notified of the problem. If you believe you have additional information that may be of help in reproducing or correcting the error, please contact Salesforce Support. Please indicate the URL of the page you were requesting, any error id shown on this page as well as any other related information. We apologize for the inconvenience.

Thank you again for your patience and assistance. And thanks for using salesforce.com!

Error ID: 1976033274-140827 (-1937271554)

So, the toSOQL() model is the best we can do today, or use ApexPages.StandardSetController, as in:

ApexPages.StandardSetController ssc = new ApexPages.StandardSetController(AccountsSelector.newInstance().selectById(accountIds));
while(true) {
    Account[] records = ssc.getRecords();
    if(ssc.getHasNext()) {
        ssc.next();
    } else {
        break;
    }
}

You could write a custom iterable/iterator class pair in order to get the above code into a more efficient form, but now you're just adding a layer of complexity to work around platform limitations.

As I just stated recently, if you're more worried about following SOLID, SOC, and other principles, rather than writing efficient code, you're missing the point of the "principles of development." You need to be willing to accept that there are limitations to the platform/language as a whole, and be willing to work within its limits.

In this case, you're trying to be too strict with SOC. The point of SOC is that something should do just one thing. In this case, the selector library is better as a QueryBuilder pattern, rather than a "return records" implementation. Also, arguably, if you're talking about SOLID and SOC, something that queries and returns records is doing two things (it's really not, but this goes to illustrate that SOC is kind of subjective by nature).

You can either accept that you may have governor limit issues with a more pure SOC implementation, or you can have a more functional code base at some cost to SOC purity. You can come up with other implementations, too, like using Callable for each row, but now you're definitely steering away from KISS.

Ultimately, SOC, SOLID, KISS, etc are all descriptions of what good code looks like, but they are not prescriptive of how you should write good code. You should attempt to be as close to the principles of development as possible, but not at the cost of performance or introducing bugs.

Answer 2

Where this is relevant, we do the following (i'm extracting code from our ContactsSelector but the principle applies equally well to AccountsSelector

public Contact[] selectByUserId(set<String> userIds) {
    if(userIds.isEmpty()){return new List<Contact>();}

    Contact[] results = Database.query(selectByUserIdAsSoql(userIds));
    return results;
}
public  Database.Querylocator selectByUserIdAsQueryLocator(Set<String> userIds) {
    return Database.getQueryLocator(selectByUserIdAsSoql(userIds));
}

private String selectByUserIdAsSoql(Set<String> userIds) {
    
    fflib_QueryFactory ctQF = newQueryFactory().setCondition('userId__c IN :userIds');
    return ctQF.toSOQL();
}

Then, the caller would do

Database.QueryLocator ql = ContactsSelector.newInstance().selectByUserIdAsQueryLocator(userIds);
Database.QueryLocatorIterator it =  ql.iterator();
while (it.hasNext()) {
  Contact c = (Contact) it.next();
}

Of course, you need not use a queryFactory in the Selector layer method, you can return a queryLocator based on inline SOQL

You are correct in that the Selector layer can't return inline SOQL.

To minimize Heap on selectors that might return large numbers of records, we have specialized fieldsets used by the queryFactories in those methods where slimmed-down result payloads matter in performance