How to make recursive function for sitetree

[Guest]

I have a table in the db which contains the page names. Each page has an ID, subpages have a parentID that is the same as the PageID and there is a sort order.

What I would like to create is a site tree layout where I can add functionalities to add pages, modify order. In fact what I have in mind is something like this:

SITE EXPLORER	categories	article	edit category	items	order
www<sitename>.					[order]
Maincat 1	[+] [-][move]	[+]	[edit]	[+] [change]
Maincat  2	[+] [-][move]	[+]	[edit]	[+] [change]	[order]
subcat 1	[+] [-][move]	[+]	[edit]	[+] [change]
subcat 2	[+] [-][move]	[+]	[edit]	[+] [change]
Maincat 3	[+] [-][move]	[+]	[edit]	[+] [change]	[order]
subcat 3	[+] [-][move]	[+]	[edit]	[+] [change]
subcat 3.1	[+] [-][move]	[+]	[edit]	[+] [change]
subcat 4	[+] [-][move]	[+]	[edit]	[+] [change]
subcat 5	[+] [-][move]	[+]	[edit]	[+] [change]
Maincat 4	[+] [-][move]	[+]	[edit]	[+] [change]

So the column Site explorer has to be created dynamically. I don't know in advanced how deep the structure goes.

Any good ideas??

Bianca

[12Robots]

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.

[Guest]

it looks logical what you are proposing I am going to try it. How do I make it look like a tree, can I use this with jQuery?

Thanks for your time!

Bianca

[-__cfSearching__-]

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

[Adam Cameron.]

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

[12Robots]

What I was going to suggest there, and frogot, is that she should consider caching the structure after writing it. That is what I do.

Jason

[-__cfSearching__-]

All good questions. I would be curious about the answers.  I was not sure if modifying the structure was an option, so I only hinted at the nested set model. But as mentioned, it does have advantages when the primary activity is reading versus writing.

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.

It is also more expensive to update. (Hence the emphasis on "more efficient for reads"). Unlike the adjacency model, changes require updating the entire tree. But the impact/expense is mitigated by less frequent modifications.

-Leigh

[Guest]

The discussion is very interesting. I put some stats on the CMS. The homepage gets hit about 5000 times a day. So each time the tree for the user should be shown. I am still not sure what approach is the best.

[Guest]

Hi Adam,

It is meant as the startpage for a CMS with about 400 users/websites. Customers are not changing the tree that much, they more often add items to a category. It is a system developed by someone else but I am managing the project now. So yes I can change the architecture.

I think the usual site has a maximum of 8 until 10 pages so the tree is not large. But it is true, in the database all categories of all sites are in one table so that makes 10*400 sites = 4000 records....

I saw several exemples of jquery trees and i was wondering if I that would be a solution.