How to make recursive function for sitetree

I'm hitting this a bit late, but some questions:

* how big with this tree get;

* I presume it'll be read far more often than it'll be updated (given it's a web app);

* how often will the reads be hierarchical (reading descendants or ancestors) as opposed to just siblings;

* are you in the position to revise the architecture?

I ask ebcause if the tree is going to be read more often than it's written, and if you're going to be doing hierarchical traversals a reasonable percentage of the time (compared to just lateral - sibling - ones), then the parent/child architecture might not be the best solution, as it's very process intensive to traverse hierarchically, as one needs to use recursion.

An alternative approach which is far more efficient for reads is the nested set model representation (google it).  The hit with nested sets is that the way they work is less obvious than the parent/child model, and also it's slightly trickier to do updates to the data.  That said, in web applications reads are performed orders of magnitude more often than writes, so this is not really such a consideration on the whole.

It might we worth investigating before you start.

--

Adam

If you happen to be using MS SQL 2005+, another option is to use CTE's. It is still using some sort of recursion internally.  But the sql is simplified to a single query. Not as elegant as nested sets. But still pretty slick.

-Leigh

Recursion then is exactly what you need.

I assume that your top level does not have any value in parentID, so that is where to start. I would do somethign like this.

<cfquery nam="parents">

WHERE parentid = ''

</cfquery>

<cfloop query="parents">

     <cfset buldLink(parent.text, parent.id) />

</cfloop>

<cffunction name="buildLink">

     <cfargument name="text" />

     <cfargument name="id" />

     <!--- Do whatever logic you need here to build the link --->

     <!--- You can output it here or return the value after the next part --->

     <!--- check for children --->

     <cfiquery name="children">

          WHERE parentid = #arguments.id# // Use cfqueryparam

     </cfquery>

     <cfif children.recordcount>

          <cfloop query="children">

               <cfset buildLink(children.text, children.id) />

          </cfloop>

     </cfif>

</cffunction>

Message was edited by: 12Robots Oops accidentally posted before I was done.