Monday, 2 November 2015

SOQL with Child Records - a loop within a loop

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:
  1. query all possible records
  2. build up a map to store all the query results
  3. when looping through the Trigger records, query against the map rather than the database each time
  4. 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
I only found this out thanks to a really helpful post on Salesforce Developers.

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.