function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
vikas rathi91vikas rathi91 

how to remove query in for loop And want to avoid for loop with in for loop

Hello Community,

I am stuck to find the solution on my Trigger . I want to Optimize my trigger code because some one use Query in for loop and nested for loop. I am abel to run code for single record but not in bulk.
Here My code please help on this, Thanks In advance.
try{
            for(opportunity opportunityobj:trigger.new)
            {
                opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id);
              if(opptyofoldmap.closeDate!=null&opptyofoldmap.closeDate!=opportunityobj.closeDate)
                {
                    Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate);
                     if(dueDate!=0 && dueDate > 0 )
                    {
                        
                        list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice, 
                                                          Revenue_Exchange_Rate__c, TCV__c, contract_term__c, CYR__c, NYR__c
                                                          from OpportunityLineItem where Opportunityid=:opportunityobj.id AND Revenue_Start_Date__c < :opportunityobj.closeDate];
                        for(OpportunityLineItem OpportunityProductobject:newopp)
                        {
                            integer i=0;
                            OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate;
                            
                            OpportunityProductupdate.add(OpportunityProductobject);
                        }
                    }
                }
            }    
        }

Thanks
Accky​
Best Answer chosen by vikas rathi91
Sampath SuranjiSampath Suranji
Hi ,
Move the Soql query outside of the for loop. Since you have to add some logic to 'Revenue_Start_Date__c' of oppLine item, try something like below,
try{
        List<OpportunityLineItem>OpportunityProductupdate= new  List<OpportunityLineItem>();
        map<ID,opportunity>oldOpps= new  map<ID,opportunity>();
        map<ID,opportunity>newOpps= new  map<ID,opportunity>();
        map<ID,opportunity>matchingOpps= new Map<ID,opportunity>();
        for(opportunity opportunityobj:trigger.new)
        {
            opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id);
            newOpps.put(opportunityobj.id,opportunityobj);
            oldOpps.put(opportunityobj.id,trigger.oldmap.get(opportunityobj.id));
            Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate);
            if(dueDate!=0 && dueDate > 0 )
            {
                matchingOpps.put(opportunityobj.id,opportunityobj);
                
            }
            
        }
        
        list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice                                                      
                                          from OpportunityLineItem where Opportunityid in : matchingOpps.values()  ];
        for(OpportunityLineItem OpportunityProductobject:newopp)
        {
            if( OpportunityProductobject.Revenue_Start_Date__c < matchingOpps.get(OpportunityProductobject.Opportunityid).closeDate){
                Integer dueDate=oldOpps.get(OpportunityProductobject.Opportunityid).closeDate.daysBetween(newOpps.get(OpportunityProductobject.Opportunityid).closeDate);
                OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate;
                
                OpportunityProductupdate.add(OpportunityProductobject);
            }
           
            
        }
        update OpportunityProductupdate;
        
    }
regards
Sampath
 

All Answers

v varaprasadv varaprasad
Hi Accky,

Please check once below snippet.
 
try{

   set<id> oppIds = new set<id>();

            for(opportunity opportunityobj:trigger.new)
            {
                opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id);
              if(opptyofoldmap.closeDate!=null&opptyofoldmap.closeDate!=opportunityobj.closeDate)
                {
                    Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate);
                     if(dueDate!=0 && dueDate > 0 )
                    {
                        oppIds.add(opportunityobj.id);
                        
                    }
                }
            }  

		list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice, 
                                                          Revenue_Exchange_Rate__c, TCV__c, contract_term__c, CYR__c, NYR__c
                                                          from OpportunityLineItem where Opportunityid in : oppIds AND Revenue_Start_Date__c < :opportunityobj.closeDate];
                        for(OpportunityLineItem OpportunityProductobject:newopp)
                        {
                            integer i=0;
                            OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate;
                            
                            OpportunityProductupdate.add(OpportunityProductobject);
                        }			
        }


Hope this helps you!
If my answer helps resolve your query, please mark it as the 'Best Answer' & upvote it to benefit others.

Thanks
Varaprasad
@For Support: varaprasad4sfdc@gmail.com


 
Sampath SuranjiSampath Suranji
Hi ,
Move the Soql query outside of the for loop. Since you have to add some logic to 'Revenue_Start_Date__c' of oppLine item, try something like below,
try{
        List<OpportunityLineItem>OpportunityProductupdate= new  List<OpportunityLineItem>();
        map<ID,opportunity>oldOpps= new  map<ID,opportunity>();
        map<ID,opportunity>newOpps= new  map<ID,opportunity>();
        map<ID,opportunity>matchingOpps= new Map<ID,opportunity>();
        for(opportunity opportunityobj:trigger.new)
        {
            opportunity opptyofoldmap=trigger.oldmap.get(opportunityobj.id);
            newOpps.put(opportunityobj.id,opportunityobj);
            oldOpps.put(opportunityobj.id,trigger.oldmap.get(opportunityobj.id));
            Integer dueDate=opptyofoldmap.closeDate.daysBetween(opportunityobj.closeDate);
            if(dueDate!=0 && dueDate > 0 )
            {
                matchingOpps.put(opportunityobj.id,opportunityobj);
                
            }
            
        }
        
        list<OpportunityLineItem> newopp=[select id,Revenue_Start_Date__c,Opportunityid, UnitPrice                                                      
                                          from OpportunityLineItem where Opportunityid in : matchingOpps.values()  ];
        for(OpportunityLineItem OpportunityProductobject:newopp)
        {
            if( OpportunityProductobject.Revenue_Start_Date__c < matchingOpps.get(OpportunityProductobject.Opportunityid).closeDate){
                Integer dueDate=oldOpps.get(OpportunityProductobject.Opportunityid).closeDate.daysBetween(newOpps.get(OpportunityProductobject.Opportunityid).closeDate);
                OpportunityProductobject.Revenue_Start_Date__c=OpportunityProductobject.Revenue_Start_Date__c+dueDate;
                
                OpportunityProductupdate.add(OpportunityProductobject);
            }
           
            
        }
        update OpportunityProductupdate;
        
    }
regards
Sampath
 
This was selected as the best answer
vikas rathi91vikas rathi91
Thanks To All For Reply I have get the solution .
@DarkCloud@DarkCloud
Hi Sampath, could you please help me out too I'm having a similar situation where I need to remove the query from for loop and for loop within for loop, below is my code. Please help me out with this. 
 
public static void afterInsert(List<Lead> newLeads,Map<ID,Lead> oldLeadMap){
        //insert subscription records on field update -  NCPC_Pardot_Avail_Sub_Interest_Ids__c
        subscriptionInterestRecords(newLeads,oldLeadMap);

    }
    
    
    //Process to create subscription from the indicated field update
    public static void subscriptionInterestRecords(List<Lead> newList,Map<ID,Lead> oldMap){
        try{            
            Map<Id,String> leadIdToAvailSubMap = new Map<Id,String>();
            if(schema.SObjectType.Lead.isAccessible()
               && schema.SObjectType.Lead.fields.NCPC_Pardot_Avail_Sub_Interest_Ids__c.isAccessible()){ 
                for(Lead leadRec : newList){
                       if( !String.isBlank(leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){ 
                           //available subscription and interest are changing
                           leadIdToAvailSubMap.put(leadRec.Id,leadRec.NCPC_Pardot_Avail_Sub_Interest_Ids__c);
                       }
                   }
  
               if(!leadIdToAvailSubMap.isEmpty()){
                              if(schema.SObjectType.ncpc__PC_Subscription__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Interest__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.isAccessible() 
                      && schema.SObjectType.ncpc__PC_Available_Subscription_Interest__c.fields.ncpc__Type__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Subscription__c.fields.ncpc__Contact__c.isAccessible()
                      && schema.SObjectType.ncpc__PC_Interest__c.fields.ncpc__Contact__c.isAccessible()
                     ){

                             for(Id leadId : leadIdToAvailSubMap.keySet()){
                                 List<String>  availSubscriptionInterestList = leadIdToAvailSubMap.get(leadId).split(',');
                                 List<ncpc__PC_Subscription__c> addSubscriptionList = new List<ncpc__PC_Subscription__c>();
                                 List<ncpc__PC_Interest__c> addInterestList = new List<ncpc__PC_Interest__c>();
                                 Set<Id> subsIdSet = new Set<Id>();
                                 Set<Id> interestIdSet = new Set<Id>();
                                 
                                 if(availSubscriptionInterestList.size()>0){
                                     for(ncpc__PC_Available_Subscription_Interest__c avail : [SELECT Id, ncpc__Type__c  FROM ncpc__PC_Available_Subscription_Interest__c 
                                                                                              WHERE Id  IN : availSubscriptionInterestList ]){
                                                                                                  if(avail.ncpc__Type__c == 'Subscription'){
                                                                                                      subsIdSet.add(avail.Id);
                                                                                                  }
                                                                                                  if(avail.ncpc__Type__c == 'Interest'){
                                                                                                      interestIdSet.add(avail.Id);
                                                                                                  }
                                                                                              }
                                     //subscriptionList
                                     for( ncpc__PC_Subscription__c sub : [SELECT Id, ncpc__Lead__c,ncpc__Opt_In__c, ncpc__Related_Subscription_Interest__c FROM ncpc__PC_Subscription__c WHERE ncpc__Lead__c =: leadId]){
                                                                              if(subsIdSet.contains(sub.ncpc__Related_Subscription_Interest__c)){
                                                                                  if(sub.ncpc__Opt_In__c == false){
                                                                                      ncpc__PC_Subscription__c s = new ncpc__PC_Subscription__c (Id=sub.Id);
                                                                                      s.ncpc__Opt_In__c = true;
                                                                                      s.ncpc__Opt_In_Date__c = Date.today();
                                                                                      addSubscriptionList.add(s);
                                                                                  }
                                                                                  //do nothing -- don't process this record and remove from set
                                                                                  subsIdSet.remove(sub.ncpc__Related_Subscription_Interest__c);
                                                                              }
                                                                          }
                                     if(subsIdSet.size()>0){
                                         //for remaining subscriptions
                                         for(Id sub : subsIdSet){
                                             ncpc__PC_Subscription__c recordSub = new ncpc__PC_Subscription__c();
                                             recordSub.ncpc__Opt_In__c = true;
                                             recordSub.ncpc__Lead__c = leadId;
                                             recordSub.ncpc__Opt_In_Source__c = 'Pardot Form';
                                             recordSub.ncpc__Related_Subscription_Interest__c = sub;
                                             addSubscriptionList.add(recordSub);
                                         }
                                     }
                                     
                                     
                                     //interestList
                                     for( ncpc__PC_Interest__c sub : [SELECT Id, ncpc__Lead__c, ncpc__Selected__c, ncpc__Interest_Selected__c  FROM ncpc__PC_Interest__c WHERE ncpc__Lead__c =: leadId]){
                                                                          if(interestIdSet.contains(sub.ncpc__Interest_Selected__c)){
                                                                              if(sub.ncpc__Selected__c == false){
                                                                                  ncpc__PC_Interest__c a = new ncpc__PC_Interest__c(Id=sub.Id);
                                                                                  a.ncpc__Selected__c = true;
                                                                                  a.ncpc__Captured_Date__c = Date.today();
                                                                                  addInterestList.add(a);
                                                                              }
                                                                              //do nothing -- don't process this record and remove from set
                                                                              interestIdSet.remove(sub.ncpc__Interest_Selected__c);
                                                                          }
                                                                      }
                                     if(interestIdSet.size()>0){
                                         //for remaining interest
                                         for(Id ins : interestIdSet){
                                             ncpc__PC_Interest__c recordSub = new ncpc__PC_Interest__c();
                                             recordSub.ncpc__Selected__c = true;
                                             recordSub.ncpc__Lead__c = leadId;
                                             recordSub.ncpc__Captured_Date__c = Date.today();
                                             recordSub.ncpc__Interest_Selected__c = ins;
                                             addInterestList.add(recordSub);
                                         }
                                     }
                                     
                                     if(addSubscriptionList.size()>0){
                                         upsert addSubscriptionList;
                                     }
                                     
                                     if(addInterestList.size()>0){
                                         upsert addInterestList;
                                     }
                                 }
                             }
                         }
                     }
               }
        }catch(Exception e){
            //catch error in some object
            System.debug(+e.getMessage() + e.getStackTraceString());
        }finally{
            //blank out NCPC_Pardot_Avail_Sub_Interest_Ids__c to handle optouts and field not getting changed
            List<Lead> updateLeadList = new List<Lead>();
            for(Lead l : newList){
                //update the field to blank once the subscription/interest records got created
                if(String.isNotBlank(l.NCPC_Pardot_Avail_Sub_Interest_Ids__c)){
                    Lead lnew = new Lead(Id=l.Id,NCPC_Pardot_Avail_Sub_Interest_Ids__c='');
                    updateLeadList.add(lnew);
                }
            }
            try{
                if(updateLeadList.size()>0)
                    update updateLeadList;
            }catch(Exception e){ System.debug(e.getStackTraceString() + ' ' + e.getMessage() );}
        }
    }
}