You need to sign in to do that
Don't have an account?
megan.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; } } } } } }
Try this.. hopefully it will work and if needed make necessary changes...I have not compiled just edited your code..
All Answers
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;
}
}
}
}
}
}
Thanks! I'm not really framilar with using maps. Would I put it in at the begining like this?
Try this.. hopefully it will work and if needed make necessary changes...I have not compiled just edited your code..
Thanks so much, I think that worked!!
Could you explain this part o the code a little? Sorry, I just want to be able to do it myself next time!
Thanks
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
Thank you! Great explanation