3

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;

  }

}
  • You need to maintain a separate List<Child__c> that contains all the child records if you want to update them. Or flatten it at the end. – Adrian Larson Jan 11 '17 at 20:23
  • Again, I modified the new code version wherein I created another List that would hold all the child records to be updated. Is it okay if you check it again and let me know if I am doing it correctly? – slsfrc Jan 11 '17 at 20:29
  • Any chance you can do this with a formula field. – John Thompson Jan 12 '17 at 1:26
5

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); 
}
| improve this answer | |
1

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);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.