+ Start a Discussion
Jayaramu T 9Jayaramu T 9 

I need to avoid SOQL query inside for loop but it is using for loop reference variable any suggetions please to avoid soql in loop . and what is the use of "system.test.isRunningTest()" in below code

List<CaseMilestone> lp_setmstones = new List<CaseMilestone>();
    for (CaseMilestoneSetting__c CMileS : CMileSs){
        id mstoneID = CMileS.MilestoneID__c;
        // Find all CaseMilestones matching our criteria in our caselist
        LIST<CaseMilestone> setmstones;
       
        if(system.test.isRunningTest())
            setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE CaseID IN:caselist AND MilestoneTypeID=:mstoneID AND CompletionDate=NULL');
        else
            setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE '+
                                                        CMileS.CaseCriteria__c+
                                                        'AND CaseID IN:caselist AND MilestoneTypeID=:mstoneID AND CompletionDate=NULL');
                                                       
        // Update the CompletionDate for each CaseMilesone
        for (CaseMilestone updatemstone : setmstones){
            updatemstone.CompletionDate= Datetime.now();
        }
        // Execute the update for all the Milestones in this set
        lp_setmstones.addAll(setmstones);
    }
Best Answer chosen by Jayaramu T 9
Steven NsubugaSteven Nsubuga
Here is the updated code.
List<CaseMilestone> lp_setmstones = new List<CaseMilestone>();

	Set<Id> mstoneIDs = new Set<Id> ();
	LIST<CaseMilestone> setmstones;
    for (CaseMilestoneSetting__c CMileS : CMileSs){
        mstoneIDs.add(CMileS.MilestoneID__c);      
    }
	// Find all CaseMilestones matching our criteria in our caselist
	if(system.test.isRunningTest()) {
		setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE CaseID IN:caselist AND MilestoneTypeID IN :mstoneIDs AND CompletionDate=NULL');
	}  
	else {
		setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE '+
                                                        CMileS.CaseCriteria__c+
                                                        'AND CaseID IN:caselist AND MilestoneTypeID IN :mstoneIDs AND CompletionDate=NULL');
	}
	
	// Update the CompletionDate for each CaseMilesone
	for (CaseMilestone updatemstone : setmstones){
		updatemstone.CompletionDate= Datetime.now();
	}
	// Execute the update for all the Milestones in this set
	lp_setmstones.addAll(setmstones);

 

All Answers

Pavit SiddhuPavit Siddhu
When this code executes by test class then test.isRunningTest()  return true  otherwise false. Store all the value from the reference field in a set and soql outside from for loop and put value in soql from Set.
cvuyyurucvuyyuru
Hi Jay,
You  need to use a map of milestoneId and list of cases to avoid SOQL Query inside the for loop.
Loop over caseMilestones related to the case and prepare this map.
 
Steven NsubugaSteven Nsubuga
Here is the updated code.
List<CaseMilestone> lp_setmstones = new List<CaseMilestone>();

	Set<Id> mstoneIDs = new Set<Id> ();
	LIST<CaseMilestone> setmstones;
    for (CaseMilestoneSetting__c CMileS : CMileSs){
        mstoneIDs.add(CMileS.MilestoneID__c);      
    }
	// Find all CaseMilestones matching our criteria in our caselist
	if(system.test.isRunningTest()) {
		setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE CaseID IN:caselist AND MilestoneTypeID IN :mstoneIDs AND CompletionDate=NULL');
	}  
	else {
		setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE '+
                                                        CMileS.CaseCriteria__c+
                                                        'AND CaseID IN:caselist AND MilestoneTypeID IN :mstoneIDs AND CompletionDate=NULL');
	}
	
	// Update the CompletionDate for each CaseMilesone
	for (CaseMilestone updatemstone : setmstones){
		updatemstone.CompletionDate= Datetime.now();
	}
	// Execute the update for all the Milestones in this set
	lp_setmstones.addAll(setmstones);

 
This was selected as the best answer
Jayaramu T 9Jayaramu T 9
Thanks Steven..
 
Jayaramu T 9Jayaramu T 9
Steven Nsubuga
At line 14 you have used for loop variable CMileS. could you please rectify the code
 
Steven NsubugaSteven Nsubuga
Hi Jayaramu, I have seen the code, and need more context since I used the code you shared. What was the use of CMileS.CaseCriteria__c?
Why did you choose to use a different query for the test? 
Will the below code work for you?
List<CaseMilestone> lp_setmstones = new List<CaseMilestone>();

	Set<Id> mstoneIDs = new Set<Id> ();
	LIST<CaseMilestone> setmstones;
    for (CaseMilestoneSetting__c CMileS : CMileSs){
        mstoneIDs.add(CMileS.MilestoneID__c);      
    }
	// Find all CaseMilestones matching our criteria in our caselist
	
		setmstones = Database.query('Select ID,MilestoneTypeID,CompletionDate FROM CaseMilestone WHERE CaseID IN:caselist AND MilestoneTypeID IN :mstoneIDs AND CompletionDate=NULL');
	  
	
	
	// Update the CompletionDate for each CaseMilesone
	for (CaseMilestone updatemstone : setmstones){
		updatemstone.CompletionDate= Datetime.now();
	}
	// Execute the update for all the Milestones in this set
	lp_setmstones.addAll(setmstones);