+ Start a Discussion
Ashu sharma 38Ashu sharma 38 

How to avoid soql inside loop?

Hi,

As My code runs perfect but as per salesforce limit getting an error.

@RestResource(urlMapping='/leadactivity/getlist/*')
global with sharing  class myActivityTask {
    
    @HttpGet
    global static  list<cust_map> doGetLead(){
        
        list<task> activityLog=new list<task> ();
        
        RestRequest req = RestContext.request;
        RestResponse res = RestContext.response;
        map<string,string> paramsMap=RestContext.request.params;
        string actdate=paramsMap.get('actdate');
        Datetime dt1 = Datetime.valueOf(actdate);
        list<cust_map> results = new list<cust_map>();        
        list<Task> taskList=[SELECT WhoId,Five9__Five9AgentName__c,createdDate,CompletedDateTime,Id,who.type FROM Task where CreatedDate>:dt1];
    

        for(task str:taskList){
            cust_map obj = new cust_map();
            if(str.who.type!='Lead')
                continue;
            
            Lead result = [SELECT SId__c FROM Lead WHERE Id = :str.WhoId];

            obj.LeadID =     str.WhoId;
            obj.CallCenterAgent = str.Five9__Five9AgentName__c;
            obj.CallDateTime = str.CompletedDateTime;
            obj.LMSID = result.LMSId__c;
            results.add(obj);
        }
       
        return results;
    }
    global class cust_map{
        Global String LeadID=null;
        Global String CallCenterAgent=null;
        Global Datetime CallDateTime=null;
        Global String LMSID = null;
    }
}

Any suggestions how to fix this.

 
Best Answer chosen by Ashu sharma 38
Deepali KulshresthaDeepali Kulshrestha
Hi satish,

I gone through your problem follow these process to avoid soql inside loop.
Apex Class:-


public class Question {

    public static List<String> testAccount()
    {
        Set<String> lstr=new Set<String>();
       List<OpportunityLineItem> loli=new List<OpportunityLineItem>([select 

Id,Name,OpportunityId,Quantity,Opportunity.Account.Name,Opportunity.AccountId 
                                                                     from OpportunityLineItem where 

Opportunity.StageName='Closed Won' and Quantity>100 ]);
        System.debug(loli);
        for(OpportunityLineItem op:loli)
        {
            if(op.Opportunity.AccountId!=null)
            {
                lstr.add(op.Opportunity.Account.Name);
            }
        }
        System.debug(lstr);
        List<String> lstrrr = new List<String>();
        lstrrr.addAll(lstr);
        return lstrrr;
    }
}


Refer bellow link:-

https://developer.salesforce.com/page/Best_Practice%3A_Avoid_SOQL_Queries_Inside_FOR_Loops


I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
www.kdeepali.com

All Answers

lnallurilnalluri
Hello Satish,

Try this.
 
@RestResource(urlMapping='/leadactivity/getlist/*')
global with sharing  class myActivityTask {
    
    @HttpGet
    global static  list<cust_map> doGetLead(){
        
        list<task> activityLog=new list<task> ();
        
        RestRequest req = RestContext.request;
        RestResponse res = RestContext.response;
        map<string,string> paramsMap=RestContext.request.params;
        Map<String,Lead> whoIdLeadMap= new Map<String,Lead>();
        List<Lead> leadList = new List<Lead>();
        List<String> whoIds = new List<String>();
        string actdate=paramsMap.get('actdate');
        Datetime dt1 = Datetime.valueOf(actdate);
        list<cust_map> results = new list<cust_map>();        
        list<Task> taskList=[SELECT WhoId,Five9__Five9AgentName__c,createdDate,CompletedDateTime,Id,who.type FROM Task where CreatedDate>:dt1];
    for(task t :taskList){
        whoIds.add(t.whoId);
    }
leadList=[SELECT Id,SId__c,LMSId__c FROM Lead WHERE Id In :whoIds];
for(Lead l :leadList){
    whoIdLeadMap.put(l.Id,l);
}
        for(task str:taskList){
            cust_map obj = new cust_map();
            if(str.who.type!='Lead')
                continue;
            
            Lead result =whoIdLeadMap.get(str.whoId);

            obj.LeadID =     str.WhoId;
            obj.CallCenterAgent = str.Five9__Five9AgentName__c;
            obj.CallDateTime = str.CompletedDateTime;
            obj.LMSID = result.LMSId__c;
            results.add(obj);
        }
       
        return results;
    }
    global class cust_map{
        Global String LeadID=null;
        Global String CallCenterAgent=null;
        Global Datetime CallDateTime=null;
        Global String LMSID = null;
    }
}

 
Ashu sharma 38Ashu sharma 38
Hi lnalluri



Still I am gettng error User-added image

Actuallr record size is more than 50000,so how to optimize?

And Thanks for Response..
lnallurilnalluri
Hi Sathish,

Which means maximum rows soql can return is 50000. Either you have to add LIMIT 49999 in the query OR you have to narrow down your query results by using more filter conditions in the WHERE clause.
Ashu sharma 38Ashu sharma 38
Hi Inalluri,

Yes i Understand,But client requirement,they need response on the basis of createdDate(by passing parameter)
example:using workbench I am passing Api
/services/apexrest/leadactivity/getlist?actdate=2019-10-11+23:01:01

and In my query 
list<Task> taskList=[SELECT WhoId,Five9__Five9AgentName__c,createdDate,CompletedDateTime,Id,who.type FROM Task where CreatedDate>:dt1]


So In this case any suggestions?
Deepali KulshresthaDeepali Kulshrestha
Hi satish,

I gone through your problem follow these process to avoid soql inside loop.
Apex Class:-


public class Question {

    public static List<String> testAccount()
    {
        Set<String> lstr=new Set<String>();
       List<OpportunityLineItem> loli=new List<OpportunityLineItem>([select 

Id,Name,OpportunityId,Quantity,Opportunity.Account.Name,Opportunity.AccountId 
                                                                     from OpportunityLineItem where 

Opportunity.StageName='Closed Won' and Quantity>100 ]);
        System.debug(loli);
        for(OpportunityLineItem op:loli)
        {
            if(op.Opportunity.AccountId!=null)
            {
                lstr.add(op.Opportunity.Account.Name);
            }
        }
        System.debug(lstr);
        List<String> lstrrr = new List<String>();
        lstrrr.addAll(lstr);
        return lstrrr;
    }
}


Refer bellow link:-

https://developer.salesforce.com/page/Best_Practice%3A_Avoid_SOQL_Queries_Inside_FOR_Loops


I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha
www.kdeepali.com
This was selected as the best answer