Blog

18/11/2015

Managing multiple multilevel account hierarchies efficiently in Apex

6 minute read

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

Picture

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:

Picture

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

Picture

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.

Picture

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:

Picture

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:

Picture

Peter Friberg

Consultant, Architect and Developer

Fluido

Read next

17/12/2015

Salesforce opportunity reporting - formulas for calculating sales performance

4 minute read

Contact us

Contact us