+ Start a Discussion
cduncombe44cduncombe44 

SOQL inside FOR loop.....need help

Hello all,

 

     So I have a simple trigger that does not work for bulk inserts because I have a SOQL statement inside of a for loop.  I know this is the reason, I just can't figure out how to accomplish it without the SOQL in the FOR loop.

 

I have created a few custom objects to create repeatable processes.  There are Process Templates, Process Template Steps, Processes, and Process Steps.  As you can guess, you create a process Template, which has process template steps, so you can then create processes that are based on those templates.  

 

I have wrote a simple trigger that each time you create a process, it creates the process steps based on the template steps.

 

trigger CreateStepsForProcess on Process__c (after insert) {

	List<Process_Step__c> StepList = new List<Process_Step__c>();
	Process_Template__c proTemp = new Process_Template__c();
    For (Process__c pro: trigger.new){    	
    	
    	proTemp = [Select Id, Name From Process_Template__c Where id =: pro.Process_Template__c];
    	For (Process_Template_Step__c tempStep: [Select Id, Name, Sequence__c, Process_Template__c, Details__c, 
    													Assign_To__c From       Process_Template_Step__c Where Process_Template__c =: proTemp.id])
    													
    	{
    		Process_Step__c step = new Process_Step__c(
    			Name = tempStep.Name,
    			Step_Details__c = tempStep.Details__c,
    			Process__c = pro.Id,
    			Sequence__c = tempStep.Sequence__c,
    			Task_Created__c = 'N'    			
    		);
    		if(step.Sequence__c == 1){
    			step.Status__c = 'Open';
    		} else {
    			step.Status__c = 'Not Started';
    		}
    		StepList.add(step);
    	}
	}
	insert StepList;
}

 

I'm sure this can be accomplished with a few different Lists and/or Maps outside of the FOR loop, I have just had no luck in accomplishing this.  Any help is greatly appreciated.  Thanks so much

 

Chris

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox
trigger createstepsforprocess on process__c (after insert) {
  process_step__c[] steps = new process_step__c[0];
  map<id,process_template__c> templates = new map<id,process_template__c>();
  for(process__c p:trigger.new) {
    templates.put(p.process_template__c,null);
  }
  templates.putall([select id,name,(select id,name,sequence__c,details__c,assign_to__c from process_template_steps__c) from process_template__c where id in :templates.keyset()]);
  for(process__c p:trigger.new) {
    if(p.process_template__c != null) {
      for(process_template_step__c pts:templates.get(p.process_template__c).process_template_steps__r) {
        steps.add(new process_step__c(
          name = pts.name, step_details__c = pts.details__c, process__c = p.id,
          sequence__c = pts.sequence__c, task_created__c = 'N',
          Status__c = pts.Sequence__c == 1 ? 'Open' : 'Not Started'));
      }
    }
  }
  insert steps;
}

This is a standard AQU (Aggregate Query Update) pattern. Here's my steps:

 

1) Aggregate. I place all of the Process_Template__c record IDs into a map called templates.

2) Query. I query all of the templates, using a subquery. The Process_Template_Pages__r bit, might need altering if your relationship name differs; check your lookup or master-detail field for the correct name, and remember it needs "__r" because it's a custom relationship.

3) Update. In this case, it's actually a massive insert, but the concept still holds. I loop through each step in the template for each process, dumping them all into one large list. Assuming I do not exceed the heap size, the last statement will insert all of the steps we've created.

 

This should probably get you most of the way there, but feel free to ask if there's anything else. Oh, and the " ? : " bit is a favorite trick of mine. It's a ternary operator, and it allows me to use a condensed if-then-else statement for the assignment to Status__c.

All Answers

sfdcfoxsfdcfox
trigger createstepsforprocess on process__c (after insert) {
  process_step__c[] steps = new process_step__c[0];
  map<id,process_template__c> templates = new map<id,process_template__c>();
  for(process__c p:trigger.new) {
    templates.put(p.process_template__c,null);
  }
  templates.putall([select id,name,(select id,name,sequence__c,details__c,assign_to__c from process_template_steps__c) from process_template__c where id in :templates.keyset()]);
  for(process__c p:trigger.new) {
    if(p.process_template__c != null) {
      for(process_template_step__c pts:templates.get(p.process_template__c).process_template_steps__r) {
        steps.add(new process_step__c(
          name = pts.name, step_details__c = pts.details__c, process__c = p.id,
          sequence__c = pts.sequence__c, task_created__c = 'N',
          Status__c = pts.Sequence__c == 1 ? 'Open' : 'Not Started'));
      }
    }
  }
  insert steps;
}

This is a standard AQU (Aggregate Query Update) pattern. Here's my steps:

 

1) Aggregate. I place all of the Process_Template__c record IDs into a map called templates.

2) Query. I query all of the templates, using a subquery. The Process_Template_Pages__r bit, might need altering if your relationship name differs; check your lookup or master-detail field for the correct name, and remember it needs "__r" because it's a custom relationship.

3) Update. In this case, it's actually a massive insert, but the concept still holds. I loop through each step in the template for each process, dumping them all into one large list. Assuming I do not exceed the heap size, the last statement will insert all of the steps we've created.

 

This should probably get you most of the way there, but feel free to ask if there's anything else. Oh, and the " ? : " bit is a favorite trick of mine. It's a ternary operator, and it allows me to use a condensed if-then-else statement for the assignment to Status__c.

This was selected as the best answer
cduncombe44cduncombe44

Wow, that worked perfectly, thank you so much.

 

I guess I don't wuite understand what you are doing with the Map.  It seems like you are putting each process template in as a key, and then I guess I don't understand how the following line works

 

templates.putall([select id,name,(select id,name,sequence__c,details__c,assign_to__c from process_template_steps__c) from process_template__c where id in :templates.keyset()]);

 

Maybe I just don't fully grasp the Map object and it's capabilities.  Could you elaborate a little more on this AQU pattern as well as maybe how the map is helping to accomplish this.

 

Again, thank you so much for the help.  It is much appreciated

 

Chris

 

sfdcfoxsfdcfox

Sure. I'm actually working on a blog article about AQU, as I like to call it, but here's a lowdown for you:

 

In the first step, I'm building a set of ID values that I want to query against. a Set is a special type of collection that will store each value only once. That means that given:

 

List<Integer> a = new List<Integer> { 1, 1, 2, 3, 5 };

Adding them to a Set results in:

 

Set<Integer> b = new Set<Integer>();
b.addAll(a);
System.debug(b); // b = {1, 2, 3, 5}

Using this knowledge, I can construct a unique list of ID values I'd like to locate by query using the most optimal set of ID values. Since a Map has a key and a value, but I do not yet have the value (which will be your Process_Template__c record), I simply store a placeholder "null" value into the map.

 

In the second phase, I use the putAll method, which is actually identical to the following code:

 

for(process_template__c temp:[select id,name,(select id,name,sequence__c,details__c,assign_to__c from process_template_steps__c) from process_template__c where id in :templates.keyset()]) {
  templates.put(temp.id, temp);
}

However, putAll is a system function, and so has a cost of 1 script statement instead of 1 script statement per row in the query, which saves me up to 199 script statements each time this trigger fires. You can only use putAll on maps with a key of Id, and a value of SObject (e.g. an Account SObject). It's a convenient function to grasp, as it can speed execution time.

 

In the third phase, we loop through the trigger records again. Here's where a map really shines. Without a map, you'd have to resort to the following logic:

 

Set<Id> templateIds = new Set<Id>();
List<Process_Template__c> templates;

for(Process__c record:Trigger.new) {
  templateIds.add(record.process_template__c);
}

templates = [select ... from process_template__c where id in :templateIds];

for(process__c record:trigger.new) {
  for(process_template__c template:templates) {
    if(record.process_template__c == template.id) {
      // do logic here
break; } } }

A loop within a loop has a maximum script count of X1 times X2 times C, where X1 is the number of elements in the first loop, and X2 is the number of elements in the second loop., and C is the number of statements within the loops Using a map cancels out the cost of X2 (because X2 is now 1), so the entire loop is reduced to a cost of X1 times C. Given 200 templates and 200 process records, you would have up to 40,000 statements executed using nested loops. Even the "break" statement to stop the first loop only reduces the maximum execution to 200 times 100 (20,000) statements, which is 100 times less efficient than using a map. In contrast, the map-based loop will always have a maximum of 200 statements times a constant for number of lines inside.

cduncombe44cduncombe44

 

Wow, Thanks for the explanation.  Really appreciate the thorough and detailed explanation.  

 

 

 

Chris