Apex Code Development You need to sign in to do that
Don't have an account?
dwwright 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?
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
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:
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... }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
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