+ Start a Discussion
birdofpreybirdofprey 

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.

 

Best Answer chosen by Admin (Salesforce Developers) 
sfcksfck

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

sfcksfck

From the code you've posted, it looks like the same Borough is set on every record in the trigger - is that correct? 

sfcksfck

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

 

 

This was selected as the best answer
birdofpreybirdofprey

thanks, I got a good enough idea to go about what i need to do.