Avoiding SOQL Query Inside For Loop

Question

I created an apex class that updates a child record every time I update a parent record. Working fine, but heard that queries should not happen inside for loops. Any help, especially sample codes to make things clearer, will be greatly appreciated.

Apex Class

public class NewClass {

    public static void NewMethod(List<Parent__c> newParent) {

        newParent = [SELECT Id, Name, Field1__c, Field2__c WHERE Id IN: newParent];

        List<Child__c> childQry = new List<Child__c>();
        List<Child__c> childListToUpdate = new List<Child__c>();

        for(Parent__c p : newParent) {

            childQry = [SELECT Id, Name, Parent__c, Field1__c, Field2__c FROM Child__c WHERE Field1__c =: p.Id];

            for(Child__c c : childQry) {

                if(p.Id != null) {
                    c.Field2__c = p.Field2__c;
                    childListToUpdate.add(c);   
                }

            } 

        }   
        update childListToUpdate;
    }

}

Apex Trigger

trigger NewTrigger on Child__c (after update) {        
    if(Trigger.isAfter && Trigger.isUpdate) {
        NewClass.NewMethod(Trigger.New);
    }
}

Updated Apex Class

public class NewClass {

  public static void NewMethod(List<Parent__c> newParent) {

    Map<Id,List<Child__c>> parentToChildren = new Map<Id,<List<Child__c>>>();
    List<Child__c> childrenToUpdate = new List<Child__c>();

    for(Child__c child : [SELECT Id, Name, Parent__c, Field1__c, Field2__c FROM Child__c WHERE Field1__c =: p.Id]) {
            if (!parentToChildren.containsKey(child.Parent__c)) {
                parentToChildren.put(child.Parent__c, new List<Child__c>());
                parentToChildren.get(child.Parent__c).add(child);
            }
    }  

    for(Parent__c p : newParent) {

        List<Child__c> children = parentToChildren.get(p.Id);

        for(Child__c c : children) {
            c.Field2__c = parentToChildren.get(c.Parent__c).Field2__c;  
            childrenToUpdate.add(c);                 
        } 

    }   
    update childrenToUpdate;

  }

}

Answer 1

You should query the records you need before your loop and cache them in a Map for retrieval later on.

Here's the basic caching mechanism (substitue Field1__c for Parent__c and newParent for parents):

Map<Id, List<Child__c>> parentToChildren = new Map<Id, List<Child__c>>();
for (Child__c child : [SELECT Parent__c FROM Child__c WHERE Parent__c IN :parents])
{
    if (!parentToChildren.containsKey(child.Parent__c))
        parentToChildren.put(child.Parent__c, new List<Child__c>());
    parentToChildren.get(child.Parent__c).add(child);
}

And here's the basic retrieval mechanism:

List<Child__c> toUpdate = new List<Child__c>();
// you need a "flattened" list for your update call

for (Parent__c parent : parents)
{
    List<Child__c> children = parentToChildren.get(parent.Id);
    // retrieve the records from your Map using a simple get call

    if (children == null) continue;
    // unlike query results, this list can be null

    for (Child__c child : children)
        child.Field2__c = parent.Field2__c;
    toUpdate.addAll(children); 
}

Answer 2

In general, when working with Apex I've found it helpful to keep the ETL process in mind. Given any business need, we need to:

1. First, query (or extract) using Soql or Sosl for all the data we'll need. I suggest querying into a map to easily reference data returned by id.
2. Second, manipulate (or transform) that data to solve your problem. You may incidentally also generate new records or objects here.
3. Finally, persist (or load) that data back into the database.

Steps 1 and 3 must be done in bulk. Query into lists or maps, insert/update lists.

Step 2 is done on a per-record level, usually in a for loop.

Tips:

1. Remember you can easily query "up" the object relationships by using dot notation and assuring your from clause is the child object. Ie: select id, account.name from contact where ... Because contact is a child of account, you can access the account's name using the relationship field dot field name. (Note, on custom objects the relationship field will end in __r)
2. You can always convert a list that you've queried into a map using this one-liner: map<id, list_type> name_of_map = new Map<id, list_type>(list_variable);