+ Start a Discussion
JimmyMacJimmyMac 

Creative Problem Solving (for Gurus only :)

In my Account object I have id and refid that relates who referred a client

 

I need to display a referal list tree in a VisualForce page as follows: (Note I just need to display the Name the other fields are to show the relationships)

 

Name=Jim id = 99

     Name=MO, id=1, refid = 99

           Name=JA, id=2, refid=1

                Name=BA, id=3, refid=2

                Name=MA, id=4,refid=2

 

     Name=MB, id=5, refid=99

          Name=CB, id=6, refid=5

                SF id=7, refid =6

                      AA id=8, refid = 7

                 SB id=9, refid=6

 

So the start is Jim, and because of Jim, the following tree has been created based on who each person has referred.

 

Since SOQL doens't have a way of doing a CONNECT BY to bring back the data in the tree order, what are your suggestions for accomplishing this?

 

I assume we need a recursive loop approach but would love to see what you gurus come up with.

 

Thanks so much.

 

Best Answer chosen by Admin (Salesforce Developers) 
ForcepowerForcepower

Here's how the controller code would look. You'll have to traverse the tree within the controller and set it up as a List for the VF page to handle. So the recursion will occur in the controller (and I don't think VF can handle recursion anyway).

 

    Map<Id, TreeNode> treeMap = new Map<Id, TreeNode>();
    List<Account> flattenedTreeNodeList = new List<Account>();    
    public void buildTree() {
    List<Account> acctList = [Select id, Name, refid, root_refid, depth from Account
                                where root_refid = :rootRefId order by depth];
        for (Account acct : acctList) {
            TreeNode node = new TreeNode(acct);
            treeMap.put(acct.id, node);
            if (acct.refid != null) {
                TreeNode parentNode = treeMap.get(acct.refid);
                if (parentNode != null) {
                    parentNode.addChild(acct.id);
                }
            }
        }            
    }                
    public class TreeNode {
        public Id nodeId { get; set; }
        public List<Id> children { get; set;}
         Account acct { get; set;}
         
         public void addChild(Id childId) {
             if (children == null) {
                 children = new List<Id>();
                 children.add(childId);
             }
         }
        public void traverse(Map<Id, TreeNode> treeMap, List<Account> flattenedTreeNodeList) {
            //process current node
            flattenedTreeNodeList.add(this.acct);
            
            if (children != null) {
                   // then process the children
                for (Id childId : children) {
                    TreeNode childNode = treeMap.get(childId);
                    if (childNode != null) //should not happen

                         // recursive call to child
                        childNode.traverse(treeMap, flattenedTreeNodeList);
                }
            }
        }
    }
Best,

Ram

All Answers

ForcepowerForcepower

JimmyMac,

 

If there is a way for you to persist the root id on all references arising from that root, it may be easier on the SOQL. Another thing that would make it easier for you to "walk" the tree would be to put a depth (or distance from root) against each node. These two attributes together can be used then to filter and sort your tree.

 

[select x, y, z from Object where ref_root = :rootId order by :depth]

 

Will have to think of a way to then walk the tree the way you have it drawn up - ie. it looks like you're going down (or up) to each leaf before taking up the next branch.

 

Best,

Ram

ForcepowerForcepower

Here's how the controller code would look. You'll have to traverse the tree within the controller and set it up as a List for the VF page to handle. So the recursion will occur in the controller (and I don't think VF can handle recursion anyway).

 

    Map<Id, TreeNode> treeMap = new Map<Id, TreeNode>();
    List<Account> flattenedTreeNodeList = new List<Account>();    
    public void buildTree() {
    List<Account> acctList = [Select id, Name, refid, root_refid, depth from Account
                                where root_refid = :rootRefId order by depth];
        for (Account acct : acctList) {
            TreeNode node = new TreeNode(acct);
            treeMap.put(acct.id, node);
            if (acct.refid != null) {
                TreeNode parentNode = treeMap.get(acct.refid);
                if (parentNode != null) {
                    parentNode.addChild(acct.id);
                }
            }
        }            
    }                
    public class TreeNode {
        public Id nodeId { get; set; }
        public List<Id> children { get; set;}
         Account acct { get; set;}
         
         public void addChild(Id childId) {
             if (children == null) {
                 children = new List<Id>();
                 children.add(childId);
             }
         }
        public void traverse(Map<Id, TreeNode> treeMap, List<Account> flattenedTreeNodeList) {
            //process current node
            flattenedTreeNodeList.add(this.acct);
            
            if (children != null) {
                   // then process the children
                for (Id childId : children) {
                    TreeNode childNode = treeMap.get(childId);
                    if (childNode != null) //should not happen

                         // recursive call to child
                        childNode.traverse(treeMap, flattenedTreeNodeList);
                }
            }
        }
    }
Best,

Ram

This was selected as the best answer
JimmyMacJimmyMac

thank you.