[SalesForce] How to avoid soql query inside for loop ? I want to use that query only for trigger.New record

I created a trigger which will insert two new records based on field update on Lead object. Every thing is working fine but to avoid governor limit I am not able to remove Soql query from for loop.

Here is my trigger handler class code :

 public class classHandler {

        public static boolean isExecuting = false;


        public static void updateValues(List<Lead> lstLead){
            List<CustomObject1__C > customObject1 = new List<CustomObject1__C >();
            List<CustomObject2__C> customObject2List = new List<CustomObject2__C>();

            String LableName;
            String Number;
            Decimal Amount;

            if( classHandler.isExecuting ){
                // if was executed durinListg the same context 
                // avoid recursion
                return;
            }

            classHandler.isExecuting = true; 
            map<Id, CustomObject1__C > mapId = new map<Id, CustomObject1__C >();

            for (Lead objLead : lstLead) {  

                if ((objLead.field1 == 'someValue'))
                {

                    Number = objLead.Number__c;
                    LableName = objLead.lable__C;

                    **// How to avoid this below queries from for loop**
                    List<Account> acclist = [Select Name, accNumber__C, ID from Account where field1__c = 'high' and  accNumber__C =: Number ];
                    List<Contact> con = [Select ID,Name from Contact where AccountId IN : acclist limit 1];               
                      if(!acclist.isEmpty()){ 
                    System.debug('Debuggg #####' + acclist);

                    }


                    if((!acclist.isEmpty()) && ( Number != null) ){
                        CustomObject1__C reg = new CustomObject1__C ();
                        reg.Name= objLead.LastName; 
                        reg.AccName = acclist[0].Name;
                        reg.contactName = con[0].Name;
                        customObject1.add(reg);



                        mapId .put(objLead.Id, reg);
                        System.debug('Debuggggg 1' + reg);
                    } 
                    else{
                        CustomObject1__C regrec = new CustomObject1__C ();
                        regrec.Name= objLead.LastName; 
                        regrec.Source__C = 'Value' ;
                        mapId .put(objLead.Id, regrec);



                    }
                }
            }
            try{
                insert customObject1;
            }

            catch(DmlException e) {
                System.debug('The following exception has occurred: ' + e.getMessage());

        }
     }
 }

Best Answer

You can use maps to achieve this.

map<String,list<Account>> accountMaps = new map<String,list<Account>>();
map<String,list<Contact>> contactMaps = new map<String,list<Contact>>();

for (Lead objLead : lstLead) {  
accountMaps.put(objLead.Number__c,new list<Account>());
contactMaps.put(objLead.Number__c,new list<Contact>());
}
for(Account acc : [Select Name, accNumber__C, ID,(Select Id from Contacts limit 1) from Account where field1__c = 'high' and  accNumber__C =: accountMaps.keySet()]){
   accountMaps.get(acc.accNumber__C).add(acc);
   contactMaps.get(acc.accNumber__C).add(acc.Contacts );
}
for(Contact con : []){
     contactMaps.get(con.accNumber__C).add(con);
}
for (Lead objLead : lstLead) {  

            if ((objLead.field1 == 'someValue'))
            {
               List<Account> acclist = accountMaps.get(objLead.Number__c);
                List<Contact> con = contactMaps.get(objLead.Number__c);               
                 if((!acclist.isEmpty()) && ( Number != null) ){
                    CustomObject1__C reg = new CustomObject1__C ();
                    reg.Name= objLead.LastName; 
                    reg.AccName = acclist[0].Name;
                    reg.contactName = con[0].Name;
                    customObject1.add(reg);



                    mapId .put(objLead.Id, reg);
                    System.debug('Debuggggg 1' + reg);
                } 
                else{
                    CustomObject1__C regrec = new CustomObject1__C ();
                    regrec.Name= objLead.LastName; 
                    regrec.Source__C = 'Value' ;
                    mapId .put(objLead.Id, regrec);



                }
            }
Related Topic