Managing multiple multilevel account hierarchies efficiently in Apex

Introduction

Managing account hierarchies, or other object hierarchies, in Apex is not a simple task. First of all you need to read all child accounts from the database starting from the top account down to lowest level of child accounts which requires multiple SOQL-queries. That’s usually fine. But what if you need to read the full account hierarchy for 5, 10 or even 50 top accounts in bulk? It would be nice to achieve this without an outer loop for the top accounts that would yield in 5 times the number of SOQL-queries, right?

Account hierarchy

The accounts are stored in the database in a hierarchy using parent ids as shown in the example below. Top accounts are A1 and B1. Children of first level are A2, A3 and B2, B3 for top accounts A1 and B1 respectively. Accounts A4, A5, A6, A7 and B4, B5 are all children on second level.

Account Hierarchies

Storage

First of all I needed to create a way to store hierarchies of accounts. Not only to store the hierarchy itself but also make it fast and efficient for traversing the tree. I came up with a combination of a custom class and a map. The key in the map is the account id for immediate retrieval of data and the value is the custom class containing each hierarchy node (account) and its immediate children (accounts).

The map is defined as follows:

    Map<Id,HierarchyNode>

And the custom class as follows. Note that the constructor will create an empty list of children by default:

public class HierarchyNode {
    Account node;
    List<HierarchyNode> children;

    HierarchyNode(Account acc) {
        node = acc;
        children = new List<HierarchyNode>();
    }
}

Accounts in a hierarchy will be stored as nodes with list of children pointing “down”, this is in contradiction to the database where nodes’ parentid points “up”. The node tree will be built on the fly as accounts are read from database one level at a time.

Account Hierarchies - Hierarchy Nodes (1)

Retrieval of accounts

To read the account hierarchy you would need to first have read all top accounts. Their immediate children can then be read by querying accounts for having its parentid equal its top account id using parentid = top_account.id. To read the full hierarchy we need to repeat this process until we reach the lowest level of child accounts. This could be accomplished by using a while loop surrounding the SOQL query.

But how do we read the full hierarchy of 5, 10 or 50 top accounts at the same time? The answer to that is by querying accounts for having their parentid in the set of their top account ids using parentid IN :top_account_ids.

The source code for the method retrieving the account hierarchies is defined as:

public static Map<Id,HierarchyNode> getHierarchies(List<Account> top_accts) {
    Map<Id,HierarchyNode> nodes = new Map<Id,HierarchyNode>();
    Set<Id> parent_acc_ids = new Set<Id>();

    for (Account a : top_accts) {
        nodes.put(a.Id, new HierarchyNode(a));
        parent_acc_ids.add(a.Id);
    }

    while (parent_acc_ids.size() > 0) {
        Map<Id,Account> subordinate_accounts =
            new Map<Id,Account>([
                SELECT Id, ParentID, Name
                FROM Account
                WHERE ParentId IN :parent_acc_ids
            ]);

        parent_acc_ids = subordinate_accounts.keySet();

        for (Account acc : subordinate_accounts.values()) {
            HierarchyNode new_node = new HierarchyNode(acc);
            nodes.put(acc.id, new_node);
            HierarchyNode ref_node = nodes.get(acc.parentId);
            ref_node.children.add(new_node);
        }
    }
    return nodes;
}

As you can see on line 1 above the method will return a map of all account hierarchy nodes and take a list of top accounts as parameter top_accts. The variable nodes on line 2 is the node tree that will be populated in this method and returned to the caller as the result of this method. The variable parent_acc_ids on line 3 is a set of parent ids that will used during the dive through each level of the hierarchy.

The top accounts are the first to be added in line 6 above as hierarchy nodes and their account ids on line 7 will serve as the first set of ids to use in first iteration on line 10 to get first level of children.

The query above on line 11 gets all children accounts on the level below all the accounts in parent_acc_ids and stores it in a map called subordinate_accounts.

Then the ids of the children are saved in line 18 above as parent_acc_ids to be used in next iteration to get their children accounts.

All retrieved child accounts will then be added as hierarchy nodes in the node tree in line 22 above. But they will also be added as children in their parents children list as stated in line 24 above.

And finally the populated hiearchy node tree is returned to the caller in line 27 above.

Traversing the account node tree

To traverse the node tree top down you start from the top account node and iterate through its children list, and for each child get its node and iterate through its children recursively down until lowest level found. On the way back from recursion all ids of the accounts will be collected into a set.

To find your way from lowest level child account (or from any other level) you start by getting the account node, get its parent id and get that parent account node and repeat that process until you reach the top account node (parentid is null).

Source code for getting and displaying the name of all children accounts of all top accounts is found below:

public static void printAllChildrenNames(List<Account> top_accts, Map<Id,HierarchyNode> nodes) {
    for (Account acc : top_accts) {
        System.debug('TOP ACCOUNT= ' + acc.name);

        HierarchyNode ref_node = nodes.get(acc.id);
        Set<Id> child_acc_ids = traverseChildren(ref_node.children, nodes);
        List<String> names = new List<String>();

        for (Id ref_id : child_acc_ids) {
            names.add(nodes.get(ref_id).node.name);
        }
    System.debug('ALL CHILDREN = ' + String.join(names, ', '));
    }
}

private static Set<Id> traverseChildren(List<HierarchyNode> children, Map<Id,HierarchyNode> nodes) {
    Set<Id> acc_ids = new Set<Id>();
    for (HierarchyNode ref_node : children) {
        acc_ids.add(ref_node.node.id);
        acc_ids.addAll(traverseChildren(ref_node.children, nodes));
    }
    return acc_ids;
}

Next steps and further optimization

After having analyzed this further I came up with a solution that will read hierarchies 4 levels at a time instead of the solution above reading one level at a time. This will save lots of SOQL-queries when digging deep into the hierarchy. I also introduced a parent node field into the HierarchyNode class to simplify navigation upwards in the node tree. Source code of new HierarchyNode class below:

// Class that is used as nodes in the account hierarchy tree built by this class
public class HierarchyNode {
    public HierarchyNode parent;
    public Account node;
    public List children;

    HierarchyNode(Account acc, HierarchyNode parent) {
        this.parent = parent;
        this.node = acc;
        this.children = new List();
    }

    HierarchyNode(Account acc) {
        this.parent = null;
        this.node = acc;
        this.children = new List();
    }
}

Accounts in a hierarchy will be stored as nodes with list of children pointing “down” and “up” (using the new parent node) as well. The node tree will be built on the fly as accounts are read from database 4 levels at a time.

Account Hierarchies - Hierarchy Nodes - With parent node

And the new quick version of reading account hierarchy from database 4 levels at a time:

// Add child nodes
private static void addChildren(List<Account> accs, Map<Id,HierarchyNode> nodes, Set<Id> parent_acc_ids) {
    for (Account acc : accs) {
        HierarchyNode ref_node = nodes.get(acc.ParentId);
        HierarchyNode new_node = new HierarchyNode(acc, ref_node);
        nodes.put(acc.id, new_node);
        if (ref_node != null) {
            ref_node.children.add(new_node);
        }
        if (parent_acc_ids != null) {
            parent_acc_ids.add(acc.id);
        }
    }
}

public static Map<Id,HierarchyNode> getAccountHierarchiesQuick(List<Account> top_accts) {
    Set<Id> parent_acc_ids = new Set<Id>();
    for (Account a : top_accts) {
        parent_acc_ids.add(a.Id);
    }

    return getAccountHierarchiesQuick(parent_acc_ids);
}

// Get complete account hierarchies, e.g. read all child accounts given
// a list of top account ids. Read accounts 4 levels at a time to speed
// up the reading and save valuable SOQL-queries
public static Map<Id,HierarchyNode> getAccountHierarchiesQuick(Set<Id> top_acct_ids) {
    Map<Id,HierarchyNode> nodes = new Map<Id,HierarchyNode>();
    Set<Id> parent_acc_ids = top_acct_ids;

    while (parent_acc_ids.size() > 0) {
        Map<Id,Account> subordinate_accounts =
        new Map<Id,Account>([
            SELECT Id, Name,
                   ParentID,
                   Parent.ParentID,
                   Parent.Parent.ParentID,
                   Parent.Parent.Parent.ParentID
            FROM   Account
            WHERE (ParentId IN :parent_acc_ids) OR
                  (Parent.ParentId IN :parent_acc_ids) OR
                  (Parent.Parent.ParentId IN :parent_acc_ids) OR
                  (Parent.Parent.Parent.ParentId IN :parent_acc_ids)]);

        List<Account> level1_accs = new List<Account>();
        List<Account> level2_accs = new List<Account>();
        List<Account> level3_accs = new List<Account>();
        List<Account> level4_accs = new List<Account>();

        // Split accounts in levels
        for (Account acc : subordinate_accounts.values()) {
            if (acc.ParentId != null) {
                if (parent_acc_ids.contains(acc.ParentId)) {
                    level1_accs.add(acc);
                }
                else if (acc.Parent.ParentId != null) {
                    if (parent_acc_ids.contains(acc.Parent.ParentId)) {
                        level2_accs.add(acc);
                    }
                    else if (acc.Parent.Parent.ParentId != null) {
                        if (parent_acc_ids.contains(acc.Parent.Parent.ParentId)) {
                            level3_accs.add(acc);
                        }
                        else if (acc.Parent.Parent.Parent.ParentId != null) {
                            if (parent_acc_ids.contains(acc.Parent.Parent.Parent.ParentId)) {
                                level4_accs.add(acc);
                            }
                        }
                    }
                }
            }
        }

        Set<Id> next_parent_acc_ids = new Set<Id>();

        // Add children on all levels found, one level at a time
        addChildren(level1_accs, nodes, null);
        addChildren(level2_accs, nodes, null);
        addChildren(level3_accs, nodes, null);
        addChildren(level4_accs, nodes, next_parent_acc_ids);

        // Use lowest level of account ids for next SOQL query
        parent_acc_ids = next_parent_acc_ids;
    }
    return nodes;
}

And to find the top account for a given account in the tree is now as easy as this:

//Get top account id for a child account id
public static Id findTopAccount(Id child_id, Map<Id,HierarchyNode> nodes) {
    HierarchyNode child_node = nodes.get(child_id);
    while (child_node.parent != null) {
        child_node = child_node.parent;
    }
    return child_node.node.id;
}
Advertisements

8 Comments on “Managing multiple multilevel account hierarchies efficiently in Apex”

  1. Dave Roberts says:

    Hi Peter,
    Many thanks for this blog. This type of solution is much appreciated as I’m just getting past the beginner stage.
    In the refined getAccountHierarchiesQuick method the top accounts haven’t been added to the nodes map so the printAllChildrenNames doesn’t work. I guess that needs a refinement, too.
    Also, could you show what a VF page would look like to report the hierarchy?
    Dave.

    Like

    • Hi Dave,
      Thanks for your comment. Yes it is true that the getAccountHierarchiesQuick method does not return the top nodes itself. It would be fairly easy to change it to be able to retrieve those nodes from the DB.

      When it comes to presenting the hierarchy in a VF-page I would recommend using the http://handlebarsjs.com/ library and make use of the partial templates. You can find a recursive example here: https://jsfiddle.net/adamboduch/5yt6M/ This could fit the account hierarchy rendering.

      Like

      • Jhonny says:

        Hi Peter im new to salesforce and learning by trial and error could you explain where to place the files in SF

        Like

      • Hi Johnny.
        These files are Apex-classes and contains source code. For a starter you will need either a developer account to play around with or a Salesforce org sandbox environment with at least Enterprise version to be able to add Apex code.

        Like

  2. Dave Roberts says:

    Thanks, Peter.
    I’ll try those ideas and post my result.

    Like

    • Try this….it will also bring in the top accounts into the hierarchy 🙂

      // Add child nodes.
      public static void addChildren(List accs, Map nodes, Set parent_acc_ids) {
      for (Account acc : accs) {
      HierarchyNode ref_node = nodes.get(acc.ParentId);
      HierarchyNode new_node = new HierarchyNode(acc, ref_node);
      nodes.put(acc.id, new_node);
      if (ref_node != null) {
      ref_node.children.add(new_node);
      }
      if (parent_acc_ids != null) {
      parent_acc_ids.add(acc.id);
      }
      }
      }

      public static Map getAccountHierarchiesQuick(List top_accts) {
      Set parent_acc_ids = new Set();
      for (Account a : top_accts) {
      parent_acc_ids.add(a.Id);
      }

      return getAccountHierarchiesQuick(parent_acc_ids, top_accts);
      }

      // Get complete account hierarchies, e.g. read all child accounts given
      // a list of top account ids. Read accounts 4 levels at a time to speed
      // up the reading and save valuable SOQL-queries
      public static Map getAccountHierarchiesQuick(Set top_acct_ids, List top_accs) {
      Map nodes = new Map();
      Set parent_acc_ids = top_acct_ids;

      for (Account top_acc : top_accs) {
      HierarchyNode new_node = new HierarchyNode(top_acc);
      nodes.put(top_acc.id, new_node);
      }

      while (parent_acc_ids.size() > 0) {
      Map subordinate_accounts =
      new Map([
      SELECT Id, Name,
      ParentID,
      Parent.ParentID,
      Parent.Parent.ParentID,
      Parent.Parent.Parent.ParentID
      FROM Account
      WHERE (ParentId IN :parent_acc_ids) OR
      (Parent.ParentId IN :parent_acc_ids) OR
      (Parent.Parent.ParentId IN :parent_acc_ids) OR
      (Parent.Parent.Parent.ParentId IN :parent_acc_ids)
      ]);

      List level1_accs = new List();
      List level2_accs = new List();
      List level3_accs = new List();
      List level4_accs = new List();

      // Split accounts in levels
      for (Account acc : subordinate_accounts.values()) {
      if (acc.ParentId != null) {
      if (parent_acc_ids.contains(acc.ParentId)) {
      level1_accs.add(acc);
      }
      else if (acc.Parent.ParentId != null) {
      if (parent_acc_ids.contains(acc.Parent.ParentId)) {
      level2_accs.add(acc);
      }
      else if (acc.Parent.Parent.ParentId != null) {
      if (parent_acc_ids.contains(acc.Parent.Parent.ParentId)) {
      level3_accs.add(acc);
      }
      else if (acc.Parent.Parent.Parent.ParentId != null) {
      if (parent_acc_ids.contains(acc.Parent.Parent.Parent.ParentId)) {
      level4_accs.add(acc);
      }
      }
      }
      }
      }
      }

      Set next_parent_acc_ids = new Set();

      // Add children on all levels found, one level at a time
      addChildren(level1_accs, nodes, null);
      addChildren(level2_accs, nodes, null);
      addChildren(level3_accs, nodes, null);
      addChildren(level4_accs, nodes, next_parent_acc_ids);

      // Use lowest level of account ids for next SOQL query
      parent_acc_ids = next_parent_acc_ids;
      }
      return nodes;
      }

      Have a nice one!

      /Peter

      Like

  3. Here is my scenario. I have a relationship of a custom object with itself but via junction object, since I need to keep some info about the relationship as well.
    Now I have to traverse from one of the children to the top most parent. Somehow I will have to keep the list of all the records of this junction object but that would limit me since I might have records more than 50000 (in future) and so I need some other way to do that.
    Please help me on this

    Like

    • So if I understand you right you have a data model consisting of a custom object without direct link, rather children connect to parent using a junction object having child and parent lookup/master-detail to parent and child custom object respectively, right? Will there ever be so that a child record will have multiple parents?

      Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s