Apex Tip: Using Maps to Reduce SOQL Calls

It's a fairly common use case for Apex, especially within the context of triggers, to need to associate a record back into a lookup – but you may be looking for a piece of information which is a step away from what you have on hand.  For instance, a recent forum post would have the user submitting related opportunity information along with a case, in a custom field, but the case's standard account field was remaining blank – they wanted it associated with the account on the opportunity instead.

Someone new to Apex development might be tempted to simply make a SOQL call to the opportunity object for every case which is inserted or updated, and then update the case.  Something like:

for(Case c : Trigger.new) {
if(c.Opportunity__c != null) {
            List<Opportunity> o  = [SELECT Id, AccountId from Opportunity WHERE Id =: c.Opportunity__c LIMIT 1]
            if(o.size() > 0 && o[0].AccountId != null) {c.AccountId = o[0].AccountId}
            }
}

Which could technically work, and pass unit tests, and get deployed to production.  But it has at least one fatal flaw: it would completely buckle under any bulk process, and tie up governor limits needlessly for other case related code.  This code in production could, and in all likelihood would, become a land mine down the road.

Instead, you can utliize maps to make an assocation of all the related opportunities with one SOQL call, and then relate the opportunities' accounts back to the case.  Simply pull all the opportunity IDs into a list, place those IDs and the associated account IDs into a map, and then you can use opportunity ID as a key to flip back the account id.  If you haven't already poked at the forum post, the complete code looks like this:

        List<Id> oppids = new List<Id>();
Map<Id,Id> oppmap = new Map<Id,Id>();
for(Case c : Trigger.new) {
oppids.add(c.Opportunity__c);
}
List<Opportunity> opps = [SELECT Id, AccountId from Opportunity where ID IN :oppids];
for(Opportunity o : opps) {
oppmap.put(o.Id,o.AccountId);
}
for(Case c : Trigger.new) {
if(c.Opportunity__c != null) {c.AccountId = oppmap.get(c.Opportunity__c);}
}

Placed into a before insert and before update trigger here.  Though the same concept lets you also reduce SOQL calls in controllers as well, depending on the situation.  You may also look out for places where formula fields could get you the related information, without requiring any code at all.

Published
April 18, 2011
Topics:

Leave your comments...

Apex Tip: Using Maps to Reduce SOQL Calls