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
megan.burchmegan.burch 

LimitException: Too many SOQL queries

Hi, 

 

I've been running this trigger in production for awhile, but it's running into governor limits for too many SOQL queries. Any suggestions on how to fix it?

 

Trigger

Trigger marketassignmenttrigger on Account (before Insert, before Update)
{
    //Iterate through all accounts passed to the trigger
    Account[] accounts = Trigger.new;
    for (Account a : accounts)
    {
        //The market on the account needs to be populated and (the record is new or (the record is updated and (the market field is changed or the geojunction is null)
        if (a.Market__c != null && (Trigger.isInsert || (Trigger.isUpdate && ((Trigger.oldMap.get(a.Id).Market__c != a.Market__c) || (a.geojunction__c == null) ))))
        { 
            //pull the geojunction ids
            List<GeoJunction__c> geojunctions = [select Id, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name = :a.Market__c];
            
            //Iterate the geo_junctions
            for (GeoJunction__c g : geojunctions)
            {
                if (g.id != null)
                {
                    //updates geojunction on account
                    a.geojunction__c = g.Id;
                    
                    if(a.ownerid == '00560000001267d' && g.Market_Assignments__r.Market_Manager__r.id != null && g.Market_Assignments__r.Market_Manager__r.IsActive  == TRUE)
                    {
                        //updates geojunction on account
                        a.ownerid = g.Market_Assignments__r.Market_Manager__r.id;
                        a.Account_Owner_hidden__c = g.Market_Assignments__r.Market_Manager__r.id;
                    }
                }
            }
        }
    }
}

 

Best Answer chosen by Admin (Salesforce Developers) 
Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Try this.. hopefully it will work and if needed make necessary changes...I have not compiled just edited your code..

 

Trigger marketassignmenttrigger on Account (before Insert, before Update)
{
	Map<string, List<GeoJunction__c>> accgeoMap = new Map<string, List<GeoJunction__c>>();
	set<string> markets = new set<string>();
	
	//Collect all the market names into a set
	for(Account a : Trigger.new){
		if (a.Market__c != null && (Trigger.isInsert || (Trigger.isUpdate && ((Trigger.oldMap.get(a.Id).Market__c != a.Market__c) || (a.geojunction__c == null) )))){
			markets.add(a.Market__c);
		}
	}
	List<GeoJunction__c> temp;
	//Get map of market to list of geoJunction
	for(GeoJunction__c g : [select Id,name, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name IN :markets]){
		temp = new List<GeoJunction__c>();
		if(!accgeoMap.containskey(g.name)){
			temp.add(g);
			accgeoMap.put(g.name,temp);
		}else{
			temp = accgeoMap.get(g.name);
			temp.add(g);
			accgeoMap.put(g.name,temp);
		}
	}
	
	//loop through all the accounts
    for(Account a : Trigger.new){
		if(accgeoMap.containskey(a.Market__c)){
			for(GeoJunction__c gg : accgeoMap.get(a.Market__c)){
				a.geojunction__c = g.Id;	
				
				if(a.ownerid == '00560000001267d' && g.Market_Assignments__r.Market_Manager__r.id != null && g.Market_Assignments__r.Market_Manager__r.IsActive  == TRUE)
                    {
                        //updates geojunction on account
                        a.ownerid = g.Market_Assignments__r.Market_Manager__r.id;
                        a.Account_Owner_hidden__c = g.Market_Assignments__r.Market_Manager__r.id;
                    }
				
			}
		}
	}
	
}

 

All Answers

Devender MDevender M

Trigger marketassignmenttrigger on Account (before Insert, before Update)
{
    //Iterate through all accounts passed to the trigger
    Account[] accounts = Trigger.new;
    for (Account a : accounts)
    {
        //The market on the account needs to be populated and (the record is new or (the record is updated and (the market field is changed or the geojunction is null)
        if (a.Market__c != null && (Trigger.isInsert || (Trigger.isUpdate && ((Trigger.oldMap.get(a.Id).Market__c != a.Market__c) || (a.geojunction__c == null) ))))
        {
            //pull the geojunction ids
            List<GeoJunction__c> geojunctions = [select Id, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name = :a.Market__c]; // Dont write query in for loop instead of this use map
            
            //Iterate the geo_junctions
            for (GeoJunction__c g : geojunctions)
            {
                if (g.id != null)
                {
                    //updates geojunction on account
                    a.geojunction__c = g.Id;
                    
                    if(a.ownerid == '00560000001267d' && g.Market_Assignments__r.Market_Manager__r.id != null && g.Market_Assignments__r.Market_Manager__r.IsActive  == TRUE)
                    {
                        //updates geojunction on account
                        a.ownerid = g.Market_Assignments__r.Market_Manager__r.id;
                        a.Account_Owner_hidden__c = g.Market_Assignments__r.Market_Manager__r.id;
                    }
                }
            }
        }
    }
}

megan.burchmegan.burch

Thanks! I'm not really framilar with using maps. Would I put it in at the begining like this? 

Trigger marketassignmenttrigger on Account (before Insert, before Update){

    List<Id> accids = new List <id>();
    Map<Id,id>, geomap = new map <id,id>();
    for (Account a : Trigger.new){
            accids.add(a.geojunction__c);
     }

 

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Try this.. hopefully it will work and if needed make necessary changes...I have not compiled just edited your code..

 

Trigger marketassignmenttrigger on Account (before Insert, before Update)
{
	Map<string, List<GeoJunction__c>> accgeoMap = new Map<string, List<GeoJunction__c>>();
	set<string> markets = new set<string>();
	
	//Collect all the market names into a set
	for(Account a : Trigger.new){
		if (a.Market__c != null && (Trigger.isInsert || (Trigger.isUpdate && ((Trigger.oldMap.get(a.Id).Market__c != a.Market__c) || (a.geojunction__c == null) )))){
			markets.add(a.Market__c);
		}
	}
	List<GeoJunction__c> temp;
	//Get map of market to list of geoJunction
	for(GeoJunction__c g : [select Id,name, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name IN :markets]){
		temp = new List<GeoJunction__c>();
		if(!accgeoMap.containskey(g.name)){
			temp.add(g);
			accgeoMap.put(g.name,temp);
		}else{
			temp = accgeoMap.get(g.name);
			temp.add(g);
			accgeoMap.put(g.name,temp);
		}
	}
	
	//loop through all the accounts
    for(Account a : Trigger.new){
		if(accgeoMap.containskey(a.Market__c)){
			for(GeoJunction__c gg : accgeoMap.get(a.Market__c)){
				a.geojunction__c = g.Id;	
				
				if(a.ownerid == '00560000001267d' && g.Market_Assignments__r.Market_Manager__r.id != null && g.Market_Assignments__r.Market_Manager__r.IsActive  == TRUE)
                    {
                        //updates geojunction on account
                        a.ownerid = g.Market_Assignments__r.Market_Manager__r.id;
                        a.Account_Owner_hidden__c = g.Market_Assignments__r.Market_Manager__r.id;
                    }
				
			}
		}
	}
	
}

 

This was selected as the best answer
megan.burchmegan.burch

Thanks so much, I think that worked!!

megan.burchmegan.burch

Could you explain this part o the code a little? Sorry, I just want to be able to do it myself next time!

 

Thanks

   List<GeoJunction__c> temp;
    //Get map of market to list of geoJunction
    for(GeoJunction__c g : [select Id,name, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name IN :markets]){
        temp = new List<GeoJunction__c>();
        if(!accgeoMap.containskey(g.name)){
            temp.add(g);
            accgeoMap.put(g.name,temp);
        }else{
            temp = accgeoMap.get(g.name);
            temp.add(g);
            accgeoMap.put(g.name,temp);

 

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

Map represents key value pairs

If you look at the map accgeoMap the key here is a string which would be (name of geojunction) and the Value is List of geoJunction__c

 

//Get map of market to list of geoJunction
for(GeoJunction__c g : [select Id,name, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive from GeoJunction__c where name IN :markets]){

 

In the above for loop I am querying all the goejunctions with the names of all the relevent markets that have been collected from Accounts.

 

temp = new List<GeoJunction__c>();
if(!accgeoMap.containskey(g.name)){

temp.add(g);
accgeoMap.put(g.name,temp);

 

In the above if loop I am checking if the current name of geojunction is already in the map..if it does not exist I am adding it to the map. The temp is list of geojuctions and I am adding to the list and putting it into the map.

 

else{

temp = accgeoMap.get(g.name);
temp.add(g);
accgeoMap.put(g.name,temp);

 

 

If the key does exist in the accgeomap i.e.,market name of the geojuction__c in the map it will come to else..here I am first getting the existing value and assiging it to temp variable and then add the next element to that list and put it into map.

 

Eg: Assume There are 2 accounts with market__c values to be "abc" and "xyz" so these two values will be in the set called markets so

markets = {"abc","xyz"}

 

Lets say there are 2 geoJunctions with name "abc" and 2 geoJuctions with name "xyz"

 

In this for loop we will have 4 records lets say the order of the records with names are "abc", "xyz", "abc", "xyz"

for(GeoJunction__c g : [select Id,name, Market_Assignments__r.Market_Manager__r.id, Market_Assignments__r.Market_Manager__r.IsActive  from GeoJunction__c where name IN :markets]){

 

Iteration 1: name = "abc" and initially map will be empty.

this if condition (  if(!accgeoMap.containskey(g.name))  ) will check if "abc" key DOES not exist and it returns true and enters into if loop

temp.add(g); --> adds to a temporary list of geojunction__c (meaning temp = {gg with name "abc"})

accgeomap.put(g.name,temp) ->above record to map accgeomap (meaning {"abc"->{gg with name "abc"} } )

 

Iteration 2: name = "xyz" and now map has a key of "abc" which was added in iteration 1

then the if condition will be checked for the key "xyz" existance.. and it does not exist because it only has the key "abc" meaning the if condition becomes true

temp.add(g); --> adds to a temporary list of geojunction__c (meaning temp = {gg with name "xyz"})

accgeomap.put(g.name,temp) ->above record to map accgeomap  meaning the map now will have two keys "abc" and "xyz" with its respective lists

 

Iteration 3: name = "abc" and remember now the map has two keys abc and xyz

hen the if condition will be checked for the key "abc" existance.. and it does exist because the map already has the key abc which was added in iteration 1and the if condition fails and enters into else condition

In the else temp = accgeoMap.get("abc"); will return the list with one element that was added in iteration 1

temp.add(g) --> will add one more element to the list and the map will be updated using accgeomap.put("abc", {gg - iteration1 + current gg});

 

the above iteration will be repeated for the fourth record..

 

Hopefully this help... not sure if I explained well... but I would recomment to understand the map concepts..

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_collections_maps.htm

 

megan.burchmegan.burch

Thank you! Great explanation