Technical notes—————CV—————Books & Readings

Basic method to avoid SOQL in the for loop (Bulkify apex code)

If you're beginner in area of Force.com development you might fall into the trap. This is very common and often trouble for all developers who start using Force.com platform. This trouble called

System.Exception: Too many SOQL queries: 101

The most confused issue with this error is that error might to appear from time to time and you might do not know about an error a pretty much time. So, what is the root of this error? The answer is simple, and this answer is one of most significant rule of Force.com platform – Do not use SOQL into a loop. Never.

In the start line when you add a trigger it seems like a very simple solution, you just add something like that:

trigger AccountTrigger on Account (before update) {
    for (Account account: Trigger.new) {
        if (Trigger.oldMap.get(account.Id).someField__c != account.someField__c) {
            List<Case> relatedCases = [ SELECT Id
                                            ,Name
                                            ,Comment
                                            ,Priority
                                            ,Category
                                        FROM Case
                                        WHERE Account =: account.Id];
            recalculateCases(relatedCases);
        }
    }
}

From the beginning this code seems okay - your users will work fine with just one record which usually be edited from within UI. But the code's behaviour will become more difficult since you started to use Data Loader or any other tools which will be a cause of BULK operation. As soon as you begin work with it you would start to get a number of issues regarding a BULK operations. It's time to Bulkify you code!

In many cases the best approach is preparation all needed data outside the loop.

Utils.cls

class CommonException extends Exception {}

public static Map<Id, List<sObject>> splitListByKey(List<sObject> sourceList, String key) {
    if (sourceList == null) {
        throw new CommonException('ERROR: splitListByKey(sourceList, key) got incorrect first parameter.');
    }
    if (String.isBlank(key)) {
        throw new CommonException('ERROR: splitListByKey(sourceList, key) got incorrect second parameter.');
    }
    Map<Id, List<sObject>> result = new Map<Id, List<sObject>>();
    List<sObject> tmpObjs;
    for (sObject obj : sourceList) {
        tmpObjs = new List<sObject>();
        if (obj.get(key) != null & amp; & amp; result.containsKey((Id)obj.get(key))) {
            tmpObjs = result.get((Id)obj.get(key));
            tmpObjs.add(obj);
            result.put((Id)obj.get(key), tmpObjs);
        } else if (obj.get(key) != null) {
            tmpObjs.add(obj);
            result.put((Id)obj.get(key), tmpObjs);
        }
    }
    return result;
}

AccountTrigger.trigger

trigger AccountTrigger on Account (before update) {
    Map<Id, List<Case>> casesByAccountId = Utils.splitListByKey([
        SELECT Id
            , Name
            , Comment
            , Priority
            , Category
            FROM Case
            WHERE Account IN : Trigger.newMap.keySet()
        ],'Account'
    );
    for (Account account : Trigger.new) {
        if (Trigger.oldMap.get(account.Id).someField__c != account.someField__c) {
            List<Case> relatedCases = casesByAccountId.get(account.Id);
            recalculateCases(relatedCases);
        }
    }
}

As you can see in the code above we use query just once. It's more flexible solution, but you need to remember that this approach should not to be your "golden hammer".

⇐ back to home