Apex Code Development You need to sign in to do that
Don't have an account?
birdofprey need advice on preventing System.LimitException: Too many SOQL queries: 101
So I have this trigger, which makes a call to a Apex class, which makes a SOQL whenver the function/method is called
Apex:
for(Integer x = 0; x < Trigger.new.size(); x++){
....
if (ZipCode != NULL && PriBoro == NULL){
ZipCodeList fillPriBoro = new ZipCodeList();
fillPriBoro.ZipCode = ZipCode;
trigger.new[x].Primary_Borough__c = fillPriBoro.getBorough();
}
....In the ZipCodeList:
public with sharing class ZipCodeList {
...
public ZipCodeList(){}
public string getBorough (){
if (ZipCode.length()>=5)
ZipCode = ZipCode.substring(0, 5);
Borough = [SELECT Borough__c FROM Address__c WHERE Zip_Code__c = :ZipCode];
if (Borough.size()>=1){
return Borough[0].Borough__c;
}else {
return NULL;
}
}
...
}So i read somewhere that in my case everytime that for loop is called from the trigger, it makes soql query call.
How would i approach this when each time i call this apex class, I alway need it to return that one Borough data.
Forget about that, I see now that you want to set the Borough for each record depending on the zip code of that record.
To avoid the error, you need to do all your database querying at the beginning. Here's one way to do it:
// get a list of all the zips you need to look up string[] allZips = new string[]{}; for (sObject thingy : trigger.new) // using sObject cos i don't know what object type is { allZips.add(thingy.zipcode); } // get all the addresses with those zips Address__c[] addresses = [select zipcode__c, borough__c from Address__c where zipcode__c in :allZips]; // make a map linking zipcode to borough map<string, string> zipBoroughs = new map<string,string>(); for (Address__c address : addresses) { zipBoroughs.put(address.zipcode__c, address.borough__c); } // Then you can loop through the trigger finding the borough in the map for (sObject thingy : trigger.new) { thingy.Primary_Borough__c = zipBoroughs.get(thingy.zipcode__c); }This is not tested and may have mistakes - I just wanted to give you an idea to get started. Happy to help more just ask
All Answers
From the code you've posted, it looks like the same Borough is set on every record in the trigger - is that correct?
Forget about that, I see now that you want to set the Borough for each record depending on the zip code of that record.
To avoid the error, you need to do all your database querying at the beginning. Here's one way to do it:
// get a list of all the zips you need to look up string[] allZips = new string[]{}; for (sObject thingy : trigger.new) // using sObject cos i don't know what object type is { allZips.add(thingy.zipcode); } // get all the addresses with those zips Address__c[] addresses = [select zipcode__c, borough__c from Address__c where zipcode__c in :allZips]; // make a map linking zipcode to borough map<string, string> zipBoroughs = new map<string,string>(); for (Address__c address : addresses) { zipBoroughs.put(address.zipcode__c, address.borough__c); } // Then you can loop through the trigger finding the borough in the map for (sObject thingy : trigger.new) { thingy.Primary_Borough__c = zipBoroughs.get(thingy.zipcode__c); }This is not tested and may have mistakes - I just wanted to give you an idea to get started. Happy to help more just ask
thanks, I got a good enough idea to go about what i need to do.