+ Start a Discussion
yervandyyervandy 

SOQL Query Inside For Loop

I have a trigger that works fine, but I have a SOQL query within a for loop. I know this is not a good idea because of APEX governer limits but I can't figure out what the syntax will be if I move it outside of the loop. Sales_Person_On_Record__c is a user lookup field. 

 

Within the for loop I am able to use op.field_name, and I'm not sure how I would use that outside the for loop. Any help would be appreciated. 

 

trigger SalesPersonOnRecord on Opportunity (before insert, before update) {
	
	for( Opportunity op : Trigger.new ) {
		String spadName; //Sales Person Associated with Deal
		String ownerName; //Name of the Opportunity Owner
		
                spadName = [select id, name from user where id = :op.Sales_Person_Associated_with_Deal__c limit 1].name;
		
		//Get the name of the opportunity owner
		ownerName = [select id, name from user where id = :op.ownerid limit 1].name;

                //Rest of the code is excluded since I only need help with the queries
      }
}

 

Chamil MadusankaChamil Madusanka

Hi,

 

trigger SalesPersonOnRecord on Opportunity (before insert, before update) {
	Set<Id> dealId = new Set<Id>(); Set<Id> ownerId = new Set<Id>(); for( Opportunity op : Trigger.new ) { dealId.add(op.Sales_Person_Associated_with_Deal__c); ownerId.add(op.ownerid); }
		
		String spadName; //Sales Person Associated with Deal
		String ownerName; //Name of the Opportunity Owner
		
        spadName = [select id, name from user where id in: dealId limit 1].name;
		
		//Get the name of the opportunity owner
		ownerName = [select id, name from user where id in: ownerId limit 1].name;

                //Rest of the code is excluded since I only need help with the queries
     
}

 If a reply to a post answers your question or resolves your problem, please mark it as the solution to the post so that others may benefit.

 

Chamil's Blog

PkSharmaPkSharma

You can try this

 

trigger SalesPersonOnRecord on Opportunity (before insert, before update)
{
    map<id,user> mpUser=new map<id,user>([select id,name from user]);
    for( Opportunity op : Trigger.new )
    {

             string ownerName ;       

             String spadName;

              spadName=mpUser.get|(op.Sales_Person_Associated_with_Deal__c).name;

             ownerName =mpUser.get(op.ownerid).name;

//Rest Code
    }
}

 

Think this will help you.



Sam27Sam27

I think p k sharma has the correct answer to it.

 

Using the map is only correct solution to the soql in loop and hence saves from soql governing limit.

yervandyyervandy

Thank you. I will try the suggestions soon and let you guys know.