• Aishwary
  • NEWBIE
  • 0 Points
  • Member since 2021

  • Chatter
    Feed
  • 0
    Best Answers
  • 1
    Likes Received
  • 0
    Likes Given
  • 1
    Questions
  • 0
    Replies
Hi, I have created a class for processing opportunity object based on the custom object "staging__c". But the issue is I'm executing SOQL inside for loop which is a bad practice. Can anyone help me to avoid it?
Here is my code for the same please have a look.
Note: My code is working. The only issue is soql which I am executing in the loop. I have commented on the issues in my code for better understanding.
global class BatchAssignment implements Database.Batchable<sObject>{
    global Database.QueryLocator start(Database.BatchableContext BC){     
        return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c 
                                         FROM staging__c]);
    }    
    
  global void execute(Database.BatchableContext BC, List<staging__c> scope){
        List<Opportunity> newOpptyList = new List<Opportunity>();
   
      for(staging__c sc:scope){
          system.debug('in for loop');
        if(sc != null && sc.Individual_or_organization__c  == 'I') {
            system.debug('in if == I');
            // calling custom method that executes soql inside loop and storing output here
            Account objAccount = searchAccount(sc);
            if(objAccount != null) {
                 // calling custom method that executes soql inside loop and storing output here
                List<Contact> linkContactList = searchContact(objAccount, sc);
                system.debug('contact'+linkContactList);
                try{
                    if(linkContactList != null && !linkContactList.isEmpty()) {
                        upsert linkContactList;
                        system.debug('creating opp');
                        //Create new opportunity;
                        for(Contact objCon : linkContactList) {
                            newOpptyList.add(new Opportunity(Name = objCon.LastName,
                                                             StageName = 'Prospecting',
                                                             AccountId = objAccount.Id,
                                                             ContactId = objCon.Id,
                                                             CLOSEDATE=Date.today()
                                                            ));                       
                            
                        }
                        
                        if(newOpptyList != null && !newOpptyList.isEmpty()) {
                            system.debug('insert opp');
                            insert newOpptyList;
                        }
                    }
                }catch(Exception ex) {
                    system.debug('---Exception--' + ex);
                }
                
            }
        }
      }
    }
    // this method is getting called inside the loop of execute method which is bad
    private Account searchAccount(staging__c scope) {
        Account acc= new Account();
        if(scope.Orgganization_Name__c != null && scope.Postal_Code__c != null) {
            system.debug('acc not zero');
            acc= [SELECT Id, Name FROM Account WHERE Name = :scope.Orgganization_Name__c AND BillingPostalCode = :scope.Postal_Code__c];
        }
        return acc;
    }
    
    // this method is getting called inside the loop of execute method which is bad
    private List<Contact> searchContact(Account objAccount, staging__c scope) {
        List<Contact> linkContactList = new List<Contact>();
        if(scope.First_Name__c != null && scope.Last_Name__c != null && scope.Postal_Code__c != null) {
            List<Contact> existingContactList = [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact 
                                                 WHERE FirstName = :scope.First_Name__c AND LastName = :scope.Last_Name__c AND MailingPostalCode = :scope.Postal_Code__c];
            //For existing contacts
            system.debug('existing contact'+existingContactList);
            if(existingContactList.size()>0 ) {
                for(Contact objCon : existingContactList) {
                    objCon.AccountId = objAccount.Id;
                    linkContactList.add(objCon);
                }
            } else {
                //create new contact
                system.debug('into else');
                linkContactList.add(new Contact(FirstName = scope.First_Name__c, 
                                                LastName = scope.Last_Name__c,
                                                MailingPostalCode = scope.Postal_Code__c,
                                                AccountId = objAccount.Id));
                system.debug('linked contact2'+linkContactList);
            }
            
        }
        return linkContactList;
    }
    
    global void finish(Database.BatchableContext BC){   
        system.debug('finished:::');
    }
}


 
Hi, I have created a class for processing opportunity object based on the custom object "staging__c". But the issue is I'm executing SOQL inside for loop which is a bad practice. Can anyone help me to avoid it?
Here is my code for the same please have a look.
Note: My code is working. The only issue is soql which I am executing in the loop. I have commented on the issues in my code for better understanding.
global class BatchAssignment implements Database.Batchable<sObject>{
    global Database.QueryLocator start(Database.BatchableContext BC){     
        return Database.getQueryLocator([SELECT Id, Individual_or_organization__c ,Orgganization_Name__c,First_Name__c,Last_Name__c,Postal_Code__c,Date_Recieved__c,Amount__c,Error_Message__c,Description__c 
                                         FROM staging__c]);
    }    
    
  global void execute(Database.BatchableContext BC, List<staging__c> scope){
        List<Opportunity> newOpptyList = new List<Opportunity>();
   
      for(staging__c sc:scope){
          system.debug('in for loop');
        if(sc != null && sc.Individual_or_organization__c  == 'I') {
            system.debug('in if == I');
            // calling custom method that executes soql inside loop and storing output here
            Account objAccount = searchAccount(sc);
            if(objAccount != null) {
                 // calling custom method that executes soql inside loop and storing output here
                List<Contact> linkContactList = searchContact(objAccount, sc);
                system.debug('contact'+linkContactList);
                try{
                    if(linkContactList != null && !linkContactList.isEmpty()) {
                        upsert linkContactList;
                        system.debug('creating opp');
                        //Create new opportunity;
                        for(Contact objCon : linkContactList) {
                            newOpptyList.add(new Opportunity(Name = objCon.LastName,
                                                             StageName = 'Prospecting',
                                                             AccountId = objAccount.Id,
                                                             ContactId = objCon.Id,
                                                             CLOSEDATE=Date.today()
                                                            ));                       
                            
                        }
                        
                        if(newOpptyList != null && !newOpptyList.isEmpty()) {
                            system.debug('insert opp');
                            insert newOpptyList;
                        }
                    }
                }catch(Exception ex) {
                    system.debug('---Exception--' + ex);
                }
                
            }
        }
      }
    }
    // this method is getting called inside the loop of execute method which is bad
    private Account searchAccount(staging__c scope) {
        Account acc= new Account();
        if(scope.Orgganization_Name__c != null && scope.Postal_Code__c != null) {
            system.debug('acc not zero');
            acc= [SELECT Id, Name FROM Account WHERE Name = :scope.Orgganization_Name__c AND BillingPostalCode = :scope.Postal_Code__c];
        }
        return acc;
    }
    
    // this method is getting called inside the loop of execute method which is bad
    private List<Contact> searchContact(Account objAccount, staging__c scope) {
        List<Contact> linkContactList = new List<Contact>();
        if(scope.First_Name__c != null && scope.Last_Name__c != null && scope.Postal_Code__c != null) {
            List<Contact> existingContactList = [SELECT Id, FirstName, LastName, MailingPostalCode FROM Contact 
                                                 WHERE FirstName = :scope.First_Name__c AND LastName = :scope.Last_Name__c AND MailingPostalCode = :scope.Postal_Code__c];
            //For existing contacts
            system.debug('existing contact'+existingContactList);
            if(existingContactList.size()>0 ) {
                for(Contact objCon : existingContactList) {
                    objCon.AccountId = objAccount.Id;
                    linkContactList.add(objCon);
                }
            } else {
                //create new contact
                system.debug('into else');
                linkContactList.add(new Contact(FirstName = scope.First_Name__c, 
                                                LastName = scope.Last_Name__c,
                                                MailingPostalCode = scope.Postal_Code__c,
                                                AccountId = objAccount.Id));
                system.debug('linked contact2'+linkContactList);
            }
            
        }
        return linkContactList;
    }
    
    global void finish(Database.BatchableContext BC){   
        system.debug('finished:::');
    }
}