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

If you greenhorn 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

[code gutter=”false”] System.Exception: Too many SOQL queries: 101 [/code]

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:

[code lang=”java”] 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); } } } [/code]

From a scratch this code is ok, your users will work fine with just one record which will be edited from UI. But the code will become more difficult, you will start 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 start to get a number of issues regarding a BULK operations. From this moment you should Bulkify you code.

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

Utils.cls

[code lang=”java”] 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 && 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; } [/code]

AccountTrigger

[code lang=”java”] 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); } } } [/code]

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 a ‘golden hammer’.