+ Start a Discussion
dwwrightdwwright 

Avoiding SOQL Queries in for Loops

I have a requirement to create a trigger that runs on the CaseComment object and does a string replacement on the contents of the comment body.

 

I need this trigger to modify CaseComments belonging to Cases of only 1 record type.

 

In order to do this, I have to check the RecordTypeId field of each CaseComment's parent Case. I'm having trouble figuring out how to do this without placing a SOQL query inside of the trigger.new for loop. I've tried two different methods:

 

   This method uses a single query to pull ALL the cases from the DB that match the RecordTypeID, then checks to see if each CaseComment matches one of those cases.

 

 

ID myCaseId = '0120000000095y7';
	//Fetch all needed Cases from the database
	List<Case> cases = [SELECT Id FROM Case WHERE RecordTypeId = :myCaseId];
	
	//For all new CaseComments,
	for(CaseComment c : trigger.new) {
		//Iterate through the cases
		for(Case c : cases) {
			//if this CaseComment matches a selected case 
			if(e.ParentID == c.Id) {
							
				//make the replacement........

 This doesn't work because I have 500+ cases and the governer limit stops me.

 

 

 

The second thing I tried to do was query the RecordTypeId of the CaseComment's parent Case directly via the relationship between them. (CaseComment.Parent.RecordTypeId)

 

        //PTI Support Case RecordTypeID
	ID myCaseId = '0120000000095y7';
	
	for(CaseComment comm : trigger.new) {

		if(comm.Parent.RecordTypeId == myCaseId) {
			//make the replacement...
		

 

 

This doesn't work because the relationship returns "null" for the RecordTypeId.

 

The trigger is running before update.

 

Does anyone have a solution to this?

Best Answer chosen by Admin (Salesforce Developers) 
ahab1372ahab1372

you dont have to query all cases. Loop through trigger.New two times:

First loop is to create a list of case ids

the query these cases and store them in a Map

Then you loop through trigger.new again and do the actual work

 

Because records enter a trigger in batches of 200, you will never query more than 200 cases.

 

Let me know if you need some pseudo code examples

All Answers

BritishBoyinDCBritishBoyinDC

Record Type Ids can be tricky, because they change from instance to Instance, unless you are using a Sandbox that contains ReocrdTypes already created in your Production Instance, in which case they are the same...so I'll suggest a way that uses Record Type Names to avoid that issue...

 

We'll create a set of Case Ids you do need to update, and then look for those as you loop through the CaseComments

 

To do that, create a Set of type Id, and then add the casecomments parent Id to that Set using a normal loop as you have below - that gives you a unique set of Ids to check against...

 

The create a list of Cases, where Id is in the Set of Ids, and RecordTypeName:

 

Case [] CheckCasesList = [Select Id from Case where RecordTYpe.Name = 'XXX']; //replace XXX with the RecordTypeName you care about

 

 

Then create a second set called CheckList, and add the Ids in this list to that Set. This Set then only contains Case Ids that we want to update

(The reason we use the Set is because it enforces uniqueness and we can check if contains a value)

 

Now in your code, as you loop through you casecomments, you can check for the parent case in the second set, and it it's there, you need to execute the update...

 

 

for(CaseComment comm : trigger.new) {

	if(checklist.contains(comm.parentid) {
			//make the replacement...

}

 

 

crop1645crop1645

Another tip -- if you need to access the record type from multiple triggers/classes, retrieve it just once via a static class method such as:

 

 

public class FlowControl {

	//	Global variables to avoid repeating SOQL calls across triggers
	public static id		discountRecordType						= null;


	public static ID getDiscountRecType () {
		if (discountRecordType == null) {
			discountRecordType = [select Id from RecordType where Name = :CONSTANTS.DISCOUNT_RECTYPE ].Id;
		}
		return discountRecordType;
	}
}

This avoids multiple SOQL calls; It also works across sandbox/PROD

 

ahab1372ahab1372

you dont have to query all cases. Loop through trigger.New two times:

First loop is to create a list of case ids

the query these cases and store them in a Map

Then you loop through trigger.new again and do the actual work

 

Because records enter a trigger in batches of 200, you will never query more than 200 cases.

 

Let me know if you need some pseudo code examples

This was selected as the best answer