0

I try to recreate the 7 Level deep Account hierarchy in my code. The code is working fine except that if the hierarchy in a real life example is too large, I get a CPU timeout error. I assume the reason is too many nested for-loops.

Could anyone suggest me how to restructure my code?

public static String displayHierarchy(String accountId) {

    List<Account> listAccounts = [SELECT Id, Name, Customer_ID__c, ShippingPostalCode, ShippingCity, ParentId, Parent.ParentId, Parent.Parent.ParentId, Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.Parent.ParentId, Parent.Name, Active__c, RAM__c  FROM Account WHERE Id = :accountId OR ParentId = :accountId OR Parent.ParentId = :accountId OR Parent.Parent.ParentId = :accountId OR Parent.Parent.Parent.ParentId = :accountId OR Parent.Parent.Parent.Parent.ParentId = :accountId OR Parent.Parent.Parent.Parent.Parent.ParentId = :accountId ORDER BY Customer_ID__c];
    List<Account> listAccountsHierarchy = new List<Account>();
    List<Account> listAccountsHierarchyL2 = new List<Account>();
    List<Account> listAccountsHierarchyL3 = new List<Account>();
    List<Account> listAccountsHierarchyL4 = new List<Account>();
    List<Account> listAccountsHierarchyL5 = new List<Account>();
    List<Account> listAccountsHierarchyL6 = new List<Account>();
    List<Account> listAccountsHierarchyL7 = new List<Account>();

    for(Account acct : listAccounts){
        if(accountId == acct.Id){
            //1st level
            listAccountsHierarchy.add(acct);
        } else if(accountId == acct.ParentId){
            //2nd level
            listAccountsHierarchyL2.add(acct);
        } else if(accountId == acct.Parent.ParentId && acct.Parent.ParentId != null){
            //3rd level
            listAccountsHierarchyL3.add(acct);
        } else if(accountId == acct.Parent.Parent.ParentId && acct.Parent.Parent.ParentId != null){
            //4th level
            listAccountsHierarchyL4.add(acct);
        } else if(accountId == acct.Parent.Parent.Parent.ParentId && acct.Parent.Parent.Parent.ParentId != null){
            //5th level
            listAccountsHierarchyL5.add(acct);
        } else if(accountId == acct.Parent.Parent.Parent.Parent.ParentId && acct.Parent.Parent.Parent.Parent.ParentId != null){
            //6th level
            listAccountsHierarchyL6.add(acct);
        } else if(accountId == acct.Parent.Parent.Parent.Parent.Parent.ParentId && acct.Parent.Parent.Parent.Parent.Parent.ParentId != null){
            //7th level
            listAccountsHierarchyL7.add(acct);
        }

    }

    //avoid CPU time limit
    for(Account acct2 : listAccountsHierarchyL2){
        if(acct2.ParentId == accountId && !listAccountsHierarchy.contains(acct2)){
            listAccountsHierarchy.add(acct2);
        }
        for(Account acct3 : listAccountsHierarchyL3){
            if(acct3.ParentId == acct2.Id && !listAccountsHierarchy.contains(acct3)){
                listAccountsHierarchy.add(acct3);
            }
            for(Account acct4 : listAccountsHierarchyL4){
                if(acct4.ParentId == acct3.Id && !listAccountsHierarchy.contains(acct4)){
                    listAccountsHierarchy.add(acct4);
                }
                for(Account acct5 : listAccountsHierarchyL5){
                    if(acct5.ParentId == acct4.Id && !listAccountsHierarchy.contains(acct5)){
                        listAccountsHierarchy.add(acct5);
                    }
                    for(Account acct6 : listAccountsHierarchyL6){
                        if(acct6.ParentId == acct5.Id && !listAccountsHierarchy.contains(acct6)){
                            listAccountsHierarchy.add(acct6);
                        }
                        for(Account acct7 : listAccountsHierarchyL7){
                            if(acct7.ParentId == acct6.Id && !listAccountsHierarchy.contains(acct7)){
                                listAccountsHierarchy.add(acct7);
                            }
        
                        }
                    }
                }
            }
        }
    }
    
    List<AccountWrapper> listAccountWrappers = new List<AccountWrapper>();
    for(Account acct : listAccountsHierarchy){

        if(acct.Customer_ID__c != null){
            String idString = String.valueOf(acct.Customer_ID__c);
            //did not work with substring
            String idSubstringLeft = idString.left(2);
            String idSubstring = idSubstringLeft.right(1);
            Integer level = Integer.valueOf(idSubstring) == 0 ? 7 : Integer.valueOf(idSubstring);

            AccountWrapper wrapper = new AccountWrapper();
            if(acct.Id == accountId){
                wrapper.isParent = true;
                wrapper.include = true;   
            } else {
                wrapper.isParent = false;
                wrapper.include = true;
            }
            wrapper.level = level;
            wrapper.account = acct; 
            listAccountWrappers.add(wrapper);
        }
    }

    return JSON.serialize(listAccountWrappers);
}
4

What you need here is a Map to keep track of the parents. I wrote an generic example in this answer and this implementation in this answer, this answer and probably one or two more I've forgotten with time. The first answer is more relevant here, but basically you need a map of each parent to its children, and then you can build the tree using a wrapper class. The map eliminates the need for much of this awkward code.

For future reference, the "nested loop anti-pattern" you should watch out for is:

for(...) {
  for(...) { 
    if(x.id == y.id) { // or some variant

If you have to do this, you need a Map object. Also, generally speaking, if you have a hierarchy, you need to use a recursive or recursive-like function to grab all of the records in order.

Conversely, this pattern is fine:

for(sObect a: aList) {
  for(sObject b: a.bList) {
    for(sObject c: b.cList) {

As long as your data is organized in a way where each parent is linked only to its children, this design won't have CPU problems. It's only when you're comparing two unrelated lists or sets as in the first example, as that causes the exponential CPU cost problem.

1
  • That seems to be in alignment with the Java TreeNode interface. Thanks for the hint to the right direction...! – Andrea Voggenreiter Dec 16 '20 at 10:46
0

In every good coding practice more that two nested loops is considered bad. Try mitigating some of the logic to formula fields or flows (triggered on create or update of records) filling fields with some data that would help you, so that you can spare most of the loops here.

The thing that comes to my mind is:

  1. One new custom number field listAccountsHierarchyLevel__c on account.
  2. On create or update if the Account has not parent that would mean it is level 1, if it has a parent then get the parents level and add 1 to get the Level of the account.

By doing this you will have account level populated by the flow of trigger or process, whatever you feel most comfortable using (I would go with a create or update triggered FLOW)

Please share if this helps or give me more details so I can give better answer

2
  • "more than 2" isn't necessarily a good indicator of excessive loops. I've used up to 5 nested loops without CPU problems. – sfdcfox Dec 16 '20 at 8:45
  • Fair enough, it is more of a my thing then. If I have to make more than two nested loops I know that I have to either rethink what am I doing or find different approach. – Boris Gichev Dec 16 '20 at 10:16

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.