Is it possible to extract the SOQL from a SOQL for Loop without loosing any advantages of using a SOQL for Loop?

Question

The Apex Developer Guide compares SOQL for Loops against Standard SOQL Queries, and highlights certain potential advantages that developers may need or want to leverage.

But putting the SOQL directly into the for statement can be ugly and hard to read, especially if you are querying for many fields, include aggregations, where clauses.

For a very contrived example (I don't actually want to count contacts!), I find it is more difficult to read this:

Map<Id, Integer> contactCountByAccountIdMap = new Map<Id, Integer>();
for (Account account : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
                    FROM Account WHERE Id IN :accountIdSet]) { 
    List<Contact> contactList = account.Contacts;
    contactCountByAccountIdMap.put(account.Id, account.Contacts.size()); 
}

than

List<Account> accountsWithContactList = [
      SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
      FROM Account 
      WHERE Id IN :accountIdSet
];
Map<Id, Integer> contactCountByAccountIdMap = new Map<Id, Integer>();
for (Account account : accountsWithContactList ) { 
        List<Contact> contactList = account.Contacts;
        contactCountByAccountIdMap.put(account.Id, account.Contacts.size()); 
}

Also (without adding the complexity of the loan pattern), you can't use a DAO/Selector object to invert control and mock out the dependency for testing.

So, if I want to leverage SOQL-for-loops, I can't do it like this:

public class DOA_AccountSelector {
    public List<Account> selectById(Set<Id> accountIdSet) {
        return [
            SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
            FROM Account 
            WHERE Id IN :accountIdSet
        ];
    }
}   

Map<Id, Integer> contactCountByAccountIdMap = new Map<Id, Integer>();
for (Account account : this.accountSelector.selectById(accountIdSet) ) { 
    List<Contact> contactList = account.Contacts;
    contactCountByAccountIdMap.put(account.Id, account.Contacts.size()); 
}

And I really wouldn't want to do this:

public interface AccountBorrowerIntf {
    void execute(Account account);
}

public class LOAN_ContactCounter implements LOAN_AccountBorrowerIntf {
    public Map<Id, Integer> contactCountByAccountIdMap {get; set;}

    public LOAN_ContactCounter() {
        this.contactCountByAccountIdMap = new Map<Id, Integer>();
    }

    public void execute(Account account) {
        List<Contact> contactList = account.Contacts;
        contactCountByAccountIdMap.put(account.Id, account.Contacts.size());        
    }
}

public class AccountSelector {
    public void borrowAccounts(AccountBorrowerIntf accountBorrower, Set<Id> accountIdSet) {
        for (Account account : [
                SELECT Id, Name, (SELECT Id, Name FROM Contacts) 
                FROM Account 
                WHERE Id IN :accountIdSet
            ]) { 
            accountBorrower.execute(account); 
        }
    }
}   

ContactCounter contactCounter = new ContactCounter();
this.accountSelector.borrowAccounts(contactCounter, accountIdSet);
Map<Id, Integer> contactCountByAccountIdMap = contactCounter.contactCountByAccountIdMap;

So, I'm wondering if there is any functional equivalent which would allow me to extract the query but allow the code to behave the same way?

Answer 1

In theory, you're supposed to be able to use a Database.QueryLocator:

Database.QueryLocator q = Database.getQueryLocator([SELECT ID FROM Account]);
...
for(SObject record: q.iterator()) {
    System.debug(record);
}

Unfortunately, for loops don't support that syntax (at least, not yet), but you can still write your own:

Database.QueryLocator q = Database.getQueryLocator([SELECT ID FROM Account]);
Database.QueryLocatorIterator iter = q.iterator();
while(iter.hasNext()) {
    Account record = (Account)iter.next();
}

This gives you the advantage of not passing around massive lists (heap size problems) while giving you the injectability feature you're looking for.

Answer 2

If you fancy using Database.query. You might need typecasting, but shouldn't be that bad.

String query ='SELECT id FROM Account LIMIT 10';

for(List<Sobject> son: Database.query(query)){
    System.debug(son);
}

Only downside: No compile time checking.