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.

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.

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.

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;
}

---