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
kiran k 12kiran k 12 

How to avoid soql query inside for loop in apex?

I have written a trigger is workin fine for small size number of records.but during bulk processing records is not working.i know the problem is soql is placed inside for loop because hitting the governor limits.how to resolve this issue?
here my code is :: 
public static void setAccountingLookup(List<SRV_Service__c> newServices,Map<Id,SRV_Service__c> mservices) {
    string strCustclassification; // to store the customer classification values
    string strProdclassification;// to store product classification values
    string strAcccustclassification; // to store accounting customer classification values
    string strAccprodclassification;  // to store accounting product classification values
    string strServiceId;  // to store the serviceid

    List<SRV_Service__c>lstservices = new List<SRV_Service__c>();
    List<SRV_Service__c> lstservicesUpdate =new List<SRV_Service__c>();
    List<ACT_Accounting__c> lstaccounting = new List<ACT_Accounting__c>();
    
    List<SRV_Service__c>lstservices1 = new List<SRV_Service__c>();
      lstservicesUpdate =[select Id,ServiceType__c,SimpleProduct__r.ProductTax__c,Accounting__c,CarSet__r.Order__r.Account.CustomerVATType__c,CarSet__r.Order__r.Account.RecordType.Name,CBServicesRepository__r.ProductClassification__c from SRV_Service__c where id =:mservices.keySet()];
    
    system.debug('## lstservicesUpdate is..::'+ lstservicesUpdate);
   
    SRV_Service__c service = new SRV_Service__c();
    for(SRV_Service__c serviceObj : lstservicesUpdate ){
        service=serviceObj;
        if(service.ServiceType__c == Label.SRV_lblOption){
            //custclass to store the customer classification values from account object
            strCustclassification = service.CarSet__r.Order__r.Account.CustomerVATType__c;
            //prodclass to store the product classification values dynamically set the value
            strProdclassification = Label.OPT_lblProdclassvalue;

        }else if(service.ServiceType__c == Label.SRV_lblCoachBuildertype){
        //custclass to store the customer classification values from account object
            strCustclassification  = service.CarSet__r.Order__r.Account.CustomerVATType__c;
        //prodclass to store the product classification values from CBService repostory object  
            strProdclassification =service.CBServicesRepository__r.ProductClassification__c;

        }else if(service.ServiceType__c == Label.SRV_lblsimpleproduct){
            //custclass to store the customer classification values from account object
            strCustclassification = service.CarSet__r.Order__r.Account.CustomerVATType__c;
            //prodclass to store the product classification values from simple product object
            strProdclassification = service.SimpleProduct__r.ProductTax__c;
        }            

        
        system.debug('## prodclass is..::'+ strProdclassification);
        lstaccounting = [select id,CustomerTax__c,ProductTax__c from ACT_Accounting__c where CustomerTax__c=:strCustclassification AND ProductTax__c =:strProdclassification LIMIT 1];
              
        
        if(!lstaccounting.isEmpty()){
            service.Accounting__c= lstaccounting[0].Id; 
            system.debug('&&&&&&&&&&&&&&&'+service.Accounting__c);
           
        }

    }
    try {
          system.debug('## lstservicesUpdate is..::'+ service);
            update service;
        }catch (Exception ex) {
            system.debug('## Exception occured while updating Service Object:: ##'+ ex);
        }
   
    }  //end of method 
    
    
    }

 
Ashish GargAshish Garg
Hi,

I have made changes to code and this should work:
  
public static void setAccountingLookup(List<SRV_Service__c> newServices,Map<Id,SRV_Service__c> mservices) {
    List<SRV_Service__c> lstservicesUpdate =new List<SRV_Service__c>();
    
	// Map to hold customer and Products classification
	Map<Id, Stirng> custClassificationMap = new Map<Id, String>();
	Map<Id, Stirng> prodClassificationMap = new Map<Id, String>();
	Set<String> strCustclassificationSet = new Set<String>();
	Set<String> strProdclassificationList = new Set<String>();
	
	List<SRV_Service__c> lstservices =[select Id,ServiceType__c,SimpleProduct__r.ProductTax__c,Accounting__c,CarSet__r.Order__r.Account.CustomerVATType__c,CarSet__r.Order__r.Account.RecordType.Name,CBServicesRepository__r.ProductClassification__c from SRV_Service__c where id =:mservices.keySet()];
    
    system.debug('## lstservices is..::'+ lstservices);
    for(SRV_Service__c serviceObj : lstservices ){
		string strCustclassification; // to store the customer classification values
		string strProdclassification;// to store product classification values
		
        if(serviceObj.ServiceType__c == Label.SRV_lblOption){
            //custclass to store the customer classification values from account object
            strCustclassification = serviceObj.CarSet__r.Order__r.Account.CustomerVATType__c;
            //prodclass to store the product classification values dynamically set the value
            strProdclassification = Label.OPT_lblProdclassvalue;

        }else if(serviceObj.ServiceType__c == Label.SRV_lblCoachBuildertype){
			//custclass to store the customer classification values from account object
            strCustclassification  = serviceObj.CarSet__r.Order__r.Account.CustomerVATType__c;
			//prodclass to store the product classification values from CBService repostory object  
            strProdclassification =serviceObj.CBServicesRepository__r.ProductClassification__c;

        }else if(serviceObj.ServiceType__c == Label.SRV_lblsimpleproduct){
            //custclass to store the customer classification values from account object
            strCustclassification = serviceObj.CarSet__r.Order__r.Account.CustomerVATType__c;
            //prodclass to store the product classification values from simple product object
            strProdclassification = serviceObj.SimpleProduct__r.ProductTax__c;
        }            
		
		custClassificationMap.put(serviceObj.Id, strCustclassification);
		prodClassificationMap.put(serviceObj.Id, strProdclassification);
        strCustclassificationSet.add(strCustclassification);
		strProdclassificationSet.add(strProdclassification);
    }
	
	List<ACT_Accounting__c> lstaccounting = [select Id, CustomerTax__c, ProductTax__c from ACT_Accounting__c where CustomerTax__c IN: strCustclassificationSet AND ProductTax__c IN: strProdclassificationSet];
	
	Map<String, ACT_Accounting__c> classifcationToActAcc = new Map<String, ACT_Accounting__c>();
	for(ACT_Accounting__c actAcc : lstaccounting) {
		classifcationToActAccId.put(actAcc.CustomerTax__c + actAcc.ProductTax__c, actAcc);
	}
	
	for(SRV_Service__c serviceObj : lstservices ){
		String uniqueClassificationStr = custClassificationMap.get(serviceObj.Id) + prodClassificationMap.get(serviceObj.Id);
		if(classifcationToActAcc.containsKey(uniqueClassificationStr)) {
			serviceObj.Accounting__c = classifcationToActAcc.get(uniqueClassificationStr).Id;
			lstservicesUpdate.add(serviceObj);
		}
	}
	
	if(lstservicesUpdate.size() > 0) {
		try {
			update lstservicesUpdate;
		}catch (Exception ex) {
			system.debug('## Exception occured while updating Service Object:: ##'+ ex);
		}
	}
	
}  //end of method