3

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?

  • Please include some code examples to give your post more context. – Adrian Larson May 24 '18 at 17:29
  • I don't have a concrete example right now, but I took an example from the Dev Guide and played around with it a bit, if that helps. – Brian Kessler May 24 '18 at 18:14
2

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.

  • Good answer! Unfortunately, I this would require my mocks to be a bit more complicated than I'd normally prefer, but trade-offs are to be expected. (It's worth noting, the DAO class could return a System.Iterator<Account> instead of Database.QueryLocatorIterator, as I can construct an instance of something which implements System.Iterator, but I can't construct a Database.QueryLocatorIterator.) – Brian Kessler May 24 '18 at 20:07
  • Iterator<Account> is just an interface which Database.QueryLocatorIterator seems to be extending, so I don't think casting should change the functionality (e.g. The method would return like this: return (System.Iterator<Account>) Database.getQueryLocator([ ... ]) )... I should probably take another look at the Stub API one of these days... I've just generally found it easier to role my own mocks without suffering its limitations. – Brian Kessler May 24 '18 at 20:28
  • 1
    @BrianKessler I wasn't sure if that was the case. If so, I suppose I should start using that design myself. – sfdcfox May 24 '18 at 20:37
  • Any good ideas how we can prove whether it is behaving one way or the other? – Brian Kessler May 24 '18 at 20:44
  • 1
    @BrianKessler Actually, I just realized that I do this all the time anyways. I actually have a modular batch process class that leverages this effect. I've just never thought about using it for this purpose. – sfdcfox May 24 '18 at 20:49
1

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.

  • While I agree that could improve readability, but you missed at least one downsides: SFDC won't know which SObjects and Fields you are using, so it won't protect them against deletion. (Also, you lose access to bind variable fields, but that's relatively easy to resolve.) – Brian Kessler May 24 '18 at 20:18
  • Oh, other "downside": It also doesn't help with dependency injection since Database.query normally returns an array and if we try to move the method invocation so we can inject it, we'll be back to working with standard for loops. – Brian Kessler May 24 '18 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.