function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ManjunathManjunath 

Removing SOQL from for loop.

Hi,

 

As per salesforce's best practice for writing trigger. It suggests to avoid SOQL inside the for loop, but there might be some scenarios where in we need to use SOQL inside. How to Avoid this?

 

Regards,

Bhawani SharmaBhawani Sharma
Collections are the best way to avoid this. Like you are working on a contact trigger. In trigger you will get contact list. Now you want to fetch all the accounts related to these contacts. So you can fetch all the accounts related to contacts and hold it in a map like:

for(Contact c : contacts)
//add c.AccountId in a Set

//Fetch Account data using set and add it in map
Map<Id, Account> mapAccounts = [Select Id, Name from Account where Id In set];

//now use this map for getting account data
ManjunathManjunath

Hi,

 

The example you have giving is again using SOQL in for loop. How to avoid that. As in my knowledge, if we add 200 contacts to the given example then this will hit governor limit.

 

(I was planning to give the same example :))

 

Regards
Bhawani SharmaBhawani Sharma

looks like, you took it in wrong way :). This is what i was explaining:

 

for(Contact c : contacts) {

//add c.AccountId in a Set
accIds.add(c.AccountId);

}

//Fetch Account data using set and add it in map
Map<Id, Account> mapAccounts = [Select Id, Name from Account where Id IN: accIds];

//Now use this Map for futher processing as this map has all the accounts data which you need.

 
ManjunathManjunath

Hi,

 

Cool This work fine for the given scenario.

 

How about for this one that I just made up. I thought posting the code will give more info than explaining.

trigger contactAcc on Contact (After insert , After Update) {
    Set<Id> AccountIds = new Set<Id>();
    for (Contact oContact  : trigger.new) {
        AccountIds.add(oContact.AccountId);
    }
    Map<Id, Account> mapAccount = new Map<Id, Account>([Select  temp__List_Name__c from Account where id IN :AccountIds ]);
    List<Account> UpdateAccount = new List<Account>();
    for(Contact C: Trigger.new){
        List<contact> Cl = new List<Contact>();
        Cl =[ Select id from Contact where Department=:c.Department];
        
        if(Cl.size()==1){
            Account Ac=mapAccount.get(C.accountid);
            Ac.temp__List_Name__c='1';
            UpdateAccount.add(Ac);
        }
        else{
            Account Ac=mapAccount.get(C.accountid);
            Ac.temp__List_Name__c='2';
            UpdateAccount.add(Ac);
        }
    
    }

    if(UpdateAccount.size()>0){
        Update UpdateAccount ;
    }
    
}

 Regards,

  
Bhawani SharmaBhawani Sharma
When adding ids in AccountIds set, at the same time populate one more set with contacts department.

Just below the Account query, create one more query:
List<Contact> contacts = [Select Id from Contact where Department in DeppartmentSet];

//loop through these records and create a Map of Contacts with department
Map<Department, List<Contact>> mapContactDeps ;4

Use this map for futher processing
hitesh90hitesh90

Hi Manhunath,

 

use below trigger i think it will work fine for your scenario.

 

Apex trigger:

trigger contactAcc on Contact (After insert , After Update) {
    Set<Id> AccountIds = new Set<Id>();
	set<string> sDepartment = new set<String>();
    for (Contact oContact  : trigger.new) {
        AccountIds.add(oContact.AccountId);
		sDepartment.add(oContact.Department);
    }
    Map<Id, Account> mapAccount = new Map<Id, Account>([Select  temp__List_Name__c from Account where id IN :AccountIds ]);
	List<contact> Cl = [Select id from Contact where Department IN: sDepartment];
	
    List<Account> UpdateAccount = new List<Account>();
    for(Contact C: Trigger.new){        
        if(Cl.size()==1){
            Account Ac=mapAccount.get(C.accountid);
            Ac.temp__List_Name__c='1';
            UpdateAccount.add(Ac);
        }
        else{
            Account Ac=mapAccount.get(C.accountid);
            Ac.temp__List_Name__c='2';
            UpdateAccount.add(Ac);
        }
    }
    if(UpdateAccount.size()>0){
        Update UpdateAccount ;
    }
    
}

 

 

 

Important :
Hit Kudos if this provides you with useful information and if this is what you where looking for then please mark it as a solution for other benefits.

Thanks,
Hitesh Patel
ManjunathManjunath

Hi ,

 

This sounds good, but there might be a case where for a Department='1' it might return more than 200 records. Then it might again hit governor limit right?

 

Thanks for your replies.

 

Regards,
Bhawani SharmaBhawani Sharma

That's why we are using Map<String, List<Contact>>, So if there are N number of contacts for a department, then it would be able to manage.
ManjunathManjunath
Sorry, I cant mark this as solution but i will Kudos.

Thanks for taking interest in replying .
Bhawani SharmaBhawani Sharma
No worry, I am here only to help. Solution Acceptance doesn't bother me.
Jerun JoseJerun Jose
Just to clarify, you got your solution right ??
ManjunathManjunath

Hi,

I feel that in certain scenarios you cannot avoid SOQL inside for loop.

What is your view  and  ?

Regards,
Jerun JoseJerun Jose
When you put it that way, yes I suppose that there could be some weird scenario where there is no other option but to perform a looped query, but as far as I have know, I have never had to do that, or even come across a scenario where we must have a query inside a for loop.

From my experience, you can always avoid using a query inside a for loop by using a proper code design and usage of collections.
Suraj Tripathi 47Suraj Tripathi 47
Hi Manjunath,

For removing SOQL from the loop you can use Map, With the help of Map you can easily avoid SOQL
from inside the loop.

If you find your Solution then mark this as the best answer. 

Thank you!

Regards 
Suraj Tripathi