Recursion Excursion !!! - Coldfusion - Advanced Techniques

[aparsons]

I have a database that looks like this:


You can see that my tree goes 3-layers deep. However, I am unable to figure
out how to recursively trace this query. I've included all source (including
the DDL) and would very much appreciate if someone could either take a look at
the code or send me to a good resource that covers tree (not using CFTREE)
navigation using Coldfusion.

Thanks again so much for your help.

http://www.allanparsons.com/upload/sidemenu_dynamic.zip

+---------+-----------------------+-----------+-----------------------+
| link_id | link | parent_id | link_desc |
+---------+-----------------------+-----------+-----------------------+
| 1 | ./dashboard.cfm | 1 | Dashboard |
| 2 | ./prodinit.cfm | 1 | Prod Init Dashboard |
| 3 | ./prodbackupaudit.cfm | 1 | Non-Prod Backup Audit |
| 4 | ./enterprise.cfm | 4 | Enterprise Tools |
| 5 | ./orgchart | 4 | ORG Chart |
| 6 | ./dblink.cfm | 4 | RHO DBLink |
| 7 | ./datafiles.cfm | 4 | Datafiles |
| 8 | ./org2.cfm | 5 | Testing 2 Deep |
+---------+-----------------------+-----------+-----------------------+

[Adam]

If you're using Oracle, you should read up on "CONNECT BY PRIOR".

But that suggestion notwithstanding, consider not using recursive
constructs for hierarchical data... do a search on "nested sets".

--
Adam

[mxstu]

aparsons,

Speaking from personal experience, I wholeheartedly agree with Adam's
suggestion about nested sets. IMO they are much more flexible and easier to
work with. Here is a good overview of nested sets if you're interested.
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427

[Iceborer]

Allow me to jump on the bandwagon with mxstu and to additionally suggest the purchase of Joe Celko's book Trees and Heirarchies in SQL for Smarties. It is an excellent resource for such things.

[aparsons]

If I do go and implement this Heirarchie in SQL, how would I create a list (using coldfusion)? Are there any examples on that code?

[mxstu]

aparsons,

There are a number of SQL query examples in the article. There are no CF examples obviously, because that is not the topic of the article. What type of list are you referring to?

[aparsons]

I've looked at that SQL code and have given it to the DBA to implement (they
don't let me touch that stuff). I'm looking for an example that takes that
code (shown in your example, MXSTU) and makes an unordered "tree" in coldfusion
( <ul> ROOT <li> ONE DEEP <ul> TWO DEEP <li> THREE DEEP </li> </ul> </li> </ul>
).

-Allan