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
ankushankush 

How to avoild soql query in for loop

Can some one please help me how to avooid the soql query in the last for loop to assign user id. Stuck on this

trigger COITrigger on Panel_Assignment__c (after insert, after update, after delete, before delete) {
    
    //If Panel Assignment is inserted then we have COI inserted along with Status as Active
    if(Trigger.isafter && trigger.isInsert){
       Set<Id> panelIds = new Set<Id>();
        list<COI_Expertise__c> coilist=new list<COI_Expertise__c>();
        for ( Panel_Assignment__c panelAssignment : Trigger.new) {
            // Store all panel's Id
            panelIds.add(panelAssignment.Panel__c);
        }
         //Below soql query fetches the related Research Applications
         //Also based on research applications the COI records are created for each reviewer assigned to the panel.
         
   list<Research_Application__c> r=[select id,Technical_Abstract__c from Research_Application__c where Panel__c=:panelIds];
       for(Research_Application__c ra:r){
           for(Panel_Assignment__c panelAssignment : Trigger.new){
        COI_Expertise__c  c=new COI_Expertise__c();
        c.Research_Application__c= ra.Id;
    c.Technical_Abstract__c=ra.Technical_Abstract__c;
           c.Active__c=true; 
         c.Reviewer_Name__c = panelAssignment.Reviewer_Name__c;
               

               user u=[select id,firstname,lastname from user where contactID=:c.Reviewer_Name__c];
                        c.OwnerId=u.Id;
           
               coilist.add(c);
                
               }
             }    
         
insert coilist;  
    }
Best Answer chosen by ankush
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
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 inserted then we have COI inserted along with Status as Active
    if (Trigger.isAfter && Trigger.isInsert) {

		Map<Id, String> panelAssignments = new Map<Id, String>();
        List<COI_Expertise__c> coilist = new List<COI_Expertise__c>();
        
		for ( Panel_Assignment__c panelAssignment : Trigger.new) {
            // Store all panel's Id and Reviewer's name
            panelAssignments.add(panelAssignment.Panel__c, panelAssignment.Reviewer_Name__c);
        }
		
        //Below soql query fetches the related Research Applications
        //Also based on research applications the COI records are created for each reviewer assigned to the panel.      
		List<Research_Application__c> researches = [SELECT 	Id,
															Technical_Abstract__c,
															Panel__c,
															ContactId
													FROM Research_Application__c 
													WHERE Panel__c =: panelAssignments.keySet()];
		
		Map<String, Id> usersId = new Map<String, Id>();
		for (User usr : [SELECT Id, ContactId FROM User WHERE ContactId =: panelAssignments.values()]) {
			usersId.put(usr.ContactId, usr.Id);
		}
		
		for (Research_Application__c research : researches) {
			COI_Expertise__c  coi = new COI_Expertise__c();
			coi.Research_Application__c = research.Id;
			coi.Technical_Abstract__c = research.Technical_Abstract__c;
			coi.Active__c = true; 
			coi.Reviewer_Name__c = panelAssignments.get(research.Panel__c);
			coi.OwnerId= usersId.get(coi.Reviewer_Name__c);
						   
			coilist.add(coi);
		}    
         
		Database.insert(coilist);  
    }
}

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
Ankit Maini.Ankit Maini.
Here is an example of a having a query inside a for loop:
trigger accountTestTrggr on Account (before insert, before update) {
	  
      //For loop to iterate through all the incoming Account records
      for(Account a: Trigger.new){
      	  /*
      	    THIS FOLLOWING QUERY IS INEFFICIENT AND DOESN'T SCALE
      	    Since the SOQL Query for related Contacts is within the FOR loop, if this trigger is initiated 
      	    with more than 20 records, the trigger will exceed the trigger governor limit
            of maximum 20 SOQL Queries.
      	  */
      	  List<Contact> contacts = [select id, salutation, firstname, lastname, email 
                        from Contact where accountId = :a.Id];
 		  
 	  for(Contact c: contacts){
 	  	  System.debug('Contact Id[' + c.Id + '], FirstName[' + c.firstname + '], 
                                         LastName[' + c.lastname +']');
 	  	  c.Description=c.salutation + ' ' + c.firstName + ' ' + c.lastname;
 	  	   /*
      	             THIS FOLLOWING DML STATEMENT IS INEFFICIENT AND DOESN'T SCALE
      	             Since the UPDATE dml operation is within the FOR loop, if this trigger is initiated 
      	             with more than 20 records, the trigger will exceed the trigger governor limit 
                     of maximum 20 DML Operations.
      	            */
      	      
 		  update c;
 	  }    	  
      }
}


Since there is a SOQL query within the for loop that iterates across all the Account objects that initiated this trigger, a query will be executed for each Account. And by default, a trigger gets a maximum of 20 SOQL queries before exceeding that governor limit. So if this trigger is invoked by a batch of more than 20 Account records, the governor limit will throw a runtime exception.

Keep in mind that the same is true for DML operations as well. Meaning, try to avoid having DML operations (insert, update, delete) inside a for Loop since that will also unnecessarily exceed the governor limit pertaining to DML operations. In this example, a trigger only allows 20 DML operations so a governor limit will be exceeded after the 20th Contact is updated.

Here is the optimal way to 'bulkify' the code to efficiently query the Contacts in a single query and only perform a single update DML operation.

trigger accountTestTrggr on Account (before insert, before update) {
  //This queries all Contacts related to the incoming Account records in a single SOQL query.
  //This is also an example of how to use child relationships in SOQL
  List<Account> accountsWithContacts = [select id, name, (select id, salutation, description, 
                                                                firstname, lastname, email from Contacts) 
                                                                from Account where Id IN :Trigger.newMap.keySet()];
	  
  List<Contact> contactsToUpdate = new List<Contact>{};
  // For loop to iterate through all the queried Account records 
  for(Account a: accountsWithContacts){
     // Use the child relationships dot syntax to access the related Contacts
     for(Contact c: a.Contacts){
   	  System.debug('Contact Id[' + c.Id + '], FirstName[' + c.firstname + '], LastName[' + c.lastname +']');
   	  c.Description=c.salutation + ' ' + c.firstName + ' ' + c.lastname; 
   	  contactsToUpdate.add(c);
     }    	  
   }
      
   //Now outside the FOR Loop, perform a single Update DML statement. 
   update contactsToUpdate;
}

Now if this trigger is invoked with a single Account record or up to 200 Account records, only 1 SOQL query and 1 update statement is executed.

Source:Best Pratice (https://developer.salesforce.com/page/Best_Practice:_Avoid_SOQL_Queries_Inside_FOR_Loops) to write trigger
ankushankush
Hi Zuinglio,

                       Thanks a lot for the guidance but the code is not compiling as I am getting the error on line 11 as 

Method does not exist or incorrect signature: [MAP&lt;Id,Id&gt;].add(Id, Id)

Infact Reviewer_Name__c is also ID field so I changed the Map<Id, String> to Map<Id, Id>  . Can you guide me here. Stuck on this.
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello,

Please correct the following line:
 
panelAssignments.put(panelAssignment.Panel__c, panelAssignment.Reviewer_Name__c);

 
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
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
Thanks Zuinglio It worked I will keep a point to follow the best practice going forward. Thanks for the guidance in right path. 
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Glad to have helped :)