SOQL and Child Records
A lot of posts regarding Apex coding focus on reducing down the number of SOQL statements you make during a Trigger.One way to achieve this which is recommended by David Liu on his blog is to:
- query all possible records
- build up a map to store all the query results
- when looping through the Trigger records, query against the map rather than the database each time
- perform a single update at the end of the Trigger - no matter how many records you are working with
Background
In a recent Trigger I also tried to reduce down the SOQL requirements even further by performing a sub query in the same statement. The official SOQL documentation can be found here.
The sub query brings back both the parent records and child records for an object in a single statement. For this project the SOQL sub query I created was:
Select id,name,CreatedById,NVMContactWorld__TimeStamp__c, NVMContactWorld__CallObjectIdentifier__c, (SELECT Name, CreatedById, CreatedDate, NVMContactWorld__Detail__c FROM NVMContactWorld__Interaction_Event_Notes__r) From NVMContactWorld__InteractionEvent__c ORDER BY NVMContactWorld__TimeStamp__c ASC NULLS FIRST
Bringing back these values in a single statement seemed like a time saver, but in order to query the child record values I had to
- set up a loop through all parent records
- set up an inner loop through all child records
The code to set up the 2 loops was:
for(NVMContactWorld__InteractionEvent__c acc:[Select id,name,CreatedById,NVMContactWorld__TimeStamp__c, NVMContactWorld__CallObjectIdentifier__c,
(SELECT Name, CreatedById, CreatedDate, NVMContactWorld__Detail__c
FROM NVMContactWorld__Interaction_Event_Notes__r)
From NVMContactWorld__InteractionEvent__c
ORDER BY NVMContactWorld__TimeStamp__c ASC NULLS FIRST]){
//System.debug('Outside of loop ' + acc.NVMContactWorld__CallObjectIdentifier__c);
combinedNote = Null;
//guid = Null;
//Loop through child records
for(NVMContactWorld__InteractionEventNote__c con:acc.NVMContactWorld__Interaction_Event_Notes__r){
//Perform updates on Child Records<
} // end inner loop
} // end outer loop
The TaskUpdate Trigger
TaskUpdate Trigger
trigger TaskUpdate on Task (before insert, before update) {
if (Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)) TaskHelper.processTasks(Trigger.new);
}
The TaskHelper Class
public with sharing class TaskHelper {
public static void processTasks(List<Task> newTasks) {
//Build a list of all users - make ID searchable
List<User> users = [Select ID, FirstName, LastName, Name from User];
Map<String, User> allUsers = new Map<String, User>();
for (User a : users) {
allUsers.put(a.ID, a);
}
System.debug('Size of allUsers is ' + allUsers.size());
//Build parent and child list of all Interaction Events and Interaction Notes
Map<String, String> allNotes = new Map<String, String>();
List <NVMContactWorld__InteractionEventNote__c> conList = New List<NVMContactWorld__InteractionEventNote__c>();
String combinedNote;
String guid;
String newGuid;
String oldGuid;
for(NVMContactWorld__InteractionEvent__c acc:[Select id,name,CreatedById,NVMContactWorld__TimeStamp__c, NVMContactWorld__CallObjectIdentifier__c,
(SELECT Name, CreatedById, CreatedDate, NVMContactWorld__Detail__c
FROM NVMContactWorld__Interaction_Event_Notes__r)
From NVMContactWorld__InteractionEvent__c
ORDER BY NVMContactWorld__TimeStamp__c ASC NULLS FIRST]){
//System.debug('Outside of loop ' + acc.NVMContactWorld__CallObjectIdentifier__c);
combinedNote = Null;
//guid = Null;
//Loop through child records
for(NVMContactWorld__InteractionEventNote__c con:acc.NVMContactWorld__Interaction_Event_Notes__r){
//Change ID for real name
User createdByName = allUsers.get(con.CreatedById);
newGuid = acc.NVMContactWorld__CallObjectIdentifier__c;
//System.debug('Debug element ' + con);
//We need to merge single records into 1
String noteDetail;
if (con.NVMContactWorld__Detail__c != Null) {
//System.debug('This agent saved no notes');
noteDetail = con.NVMContactWorld__Detail__c;
}
else {
noteDetail = 'This agent saved no notes';
}
conList.add(con);
//Create the actual string
combinedNote = createdByName.Name + ' | on ' + con.CreatedDate + ' | ' + noteDetail + '\r\n';
guid = acc.NVMContactWorld__CallObjectIdentifier__c;
if (allNotes.get(acc.NVMContactWorld__CallObjectIdentifier__c) == Null) {
//System.debug('Map not created - create it');
allNotes.put(guid, combinedNote);
}
else {
//System.debug('Map already created - update it');
String oldNotes = allNotes.get(acc.NVMContactWorld__CallObjectIdentifier__c);
//System.debug('oldNotes is ' + oldNotes);
allNotes.put(acc.NVMContactWorld__CallObjectIdentifier__c, oldNotes + ' \r\n' + combinedNote);
//System.debug('allNotes is ' + allNotes.values());
}
} //end outer for
}
//do we need to touch this record?
Boolean validRecordsFound = false;
System.debug('Starting class');
//Loop through Task records and get GUIDs to query Interaction Events - checking that it is only NVM Tasks
for (Task ss : newTasks) {
if (ss.CallType != Null && ss.CallObject != Null) {
//We found a NVM task - there is work to do
validRecordsFound = True;
//Final call to the built map to populate description
String oldValue = ss.Description;
If (ss.Description == Null) {
ss.Description = '\r\n--------Save your notes above this line--------\r\n' + allNotes.get(ss.CallObject);
}
else if (oldValue.contains('--------Save your notes above this line--------')) {
Integer thingsToRemove = oldValue.indexOf('--------Save your notes above this line--------');
System.debug('Remove string after position ' + thingsToRemove);
System.debug('Previous call notes found');
ss.Description = oldValue.left(thingsToRemove) + '\r\n--------Save your notes above this line--------\r\n' + allNotes.get(ss.CallObject);
}
else {
String newValue = oldValue + '\r\n--------Save your notes above this line--------\r\n' + allNotes.get(ss.CallObject);
ss.Description = newValue;
}
} //end if
else {
//System.debug('No work to do');
}
} //end for
} //end method
} //end class
The Output
After all of that processing, the Trigger works through the child records attached to this parent record and populates a single field with all of their results.
Icon by Designmodo: from Iconfinder
Comments
Post a Comment