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());

        }
     }
 }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);



                }
            }

Method 2

Gather your Number variable into a collection in a for loop outside of your current for loop then perform your queries.

Set<dataType> numbers = new Set<dataType>();
for (Lead l : leads) {
   numbers.add(l.Number__c);
}

Then query using in.

Then do your business logic.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0