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
ankushankush 

To Avoid soql queries inside for loops

Can some one please help me how to avoid soql queries inside for loop. The below code working fine. Same functionality to work without soql queries iside for loop. Stuck on this.
 
if(trigger.isbefore && trigger.isdelete)
    {
     
       list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
       list<panel_assignment__c> lp=[select Reviewer_Name__c,Panel__c,Active__c from panel_assignment__C where id=:Trigger.old];
        system.debug('The list is' +  lp);
   for(panel_assignment__c l:lp){
       Id ReviewerId=l.Reviewer_Name__c;
    Id PanelId=l.Panel__c;
   list<Research_Application__c> r=[select id from Research_Application__c where Panel__c=:panelId];
       for(Research_Application__c ra:r){
       Id rschappId=ra.Id;
           list<COI_Expertise__c> ci=[Select id,Reviewer_Name__c from COI_Expertise__c where Research_Application__c=:rschappId];
           for(COI_Expertise__c ce:ci){
               if(ReviewerId==ce.Reviewer_Name__c)
       ce.Active__c=false;
       coilist.add(ce);
   }
           }
       }
        update coilist;
    }
Best Answer chosen by ankush
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

Try the code below:
  
trigger COITrigger on Panel_Assignment__c (after insert, after update, after delete, before delete) {
    
	 //If Panel Assignment is deleted then COI Active status is set to false.
    if(Trigger.isBefore && Trigger.isDelete)
    {
		Set<Id> panelIds = new Set<Id>();
		for ( Panel_Assignment__c panelAssignment : Trigger.old) {
			// Store all panel's Id
			panelIds.add(panelAssignment.Panel__c);
		}
		
		// Mass updates records avoiding heap limit.
		// https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm
		for (List<COI_Expertise__c> cois : [SELECT  Id,
													Active__c
											FROM COI_Expertise__c
											WHERE Research_Application__r.Panel__c =: panelIds]) {
			for (COI_Expertise__c coi : cois) {
				coi.Active__c = false;
			}

			Database.update(cois);
		}		
}

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

All Answers

Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

Try the code below:
  
trigger COITrigger on Panel_Assignment__c (after insert, after update, after delete, before delete) {
    
	 //If Panel Assignment is deleted then COI Active status is set to false.
    if(Trigger.isBefore && Trigger.isDelete)
    {
		Set<Id> panelIds = new Set<Id>();
		for ( Panel_Assignment__c panelAssignment : Trigger.old) {
			// Store all panel's Id
			panelIds.add(panelAssignment.Panel__c);
		}
		
		// Mass updates records avoiding heap limit.
		// https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm
		for (List<COI_Expertise__c> cois : [SELECT  Id,
													Active__c
											FROM COI_Expertise__c
											WHERE Research_Application__r.Panel__c =: panelIds]) {
			for (COI_Expertise__c coi : cois) {
				coi.Active__c = false;
			}

			Database.update(cois);
		}		
}

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
This was selected as the best answer
ankushankush
One more small query on how to compare coi.Reviewer_Name__c ==trigger.old.Reviewer name as I am getting the comparision error
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

When you are deleting a record you only have Trigger.old available so you cannot compare it. If it was an insert or update it would be like the code below: 
// inside Trigger.new FOR loop
coi.Reviewer_Name__c  == Trigger.oldMap.get(coi.Id).Reviewer_Name__c

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.