Nested Set Trees in ColdFusion (v1.0)
A common technique to manage hierarchical data in a relational database is to use an "Adjacency List" model, where you have both an ID column and a Parent ID column in a table. This is easy to understand and maintain but can be difficult or inefficient when you want to retrieve hierarchies of records.
The "Nested Set" model provides an alternative technique for managing this kind of data and is more efficient at reading a hierarchy but requires a little more work for inserts, updates, deletes and moves.
This project provides a library of ColdFusion code that you may like to use to help manage your hierarchical data using the nested set model.
Links
Download from here:
http://nstree.riaforge.org
Documentation is here:
http://stannard.net.au/blog/page.cfm/nested-set-trees
Changes in this release
- Fix category gateway getCategorySubtree() and getCategoryChildren() bugs in the demo app.
- Add an MySQL script for the demo app (thanks to Will Tomlinson).
Please add any comments/problems regarding this release to this entry, thanks.
Thanks To ...
A few people have helped find and fix problems as well as provide great feedback. So thanks to Hussein Grant, Laker Netman, automaxion, Will Tomlinson, Marwan Narian, Adam Cameron and Dan Sorensen
References
Nested Set Trees, a PHP/mysql implementation
http://www.edutech.ch/contribution/nstrees/index.php
Managing Hierarchical Data in MySQL
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Storing Hierarchical Data in a Database
http://www.sitepoint.com/article/hierarchical-data-database
Using the Nested Set Data Model for Breadcrumb Links
http://www.developer.com/db/article.php/3517366
A Look at SQL Trees (Joe Celko)
http://www.dbmsmag.com/9603d06.html
Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4
Thanks for letting me know. Should have a download link now.
http://nstree.riaforge.org/
it make tree a total walk in the park
wish the other dbs would support it
This is great!
http://www.sqlteam.com/article/more-trees-hierarch...
For anyone reading, the link John provided demonstrates an alternative technique for managing hierarchical data which is based on maintaining a "lineage" column which is essentially a string of delimited values.
I have Celko's "Trees And Hierarchies..." book which provides an SQL-92 query that outlines a solution, but I am having trouble getting it working in MSSQL 2000.
Can anyone post a query or link to an article that might help?
TIA,
Laker
You might be able to get this working using a slight variation of the query that you'd use for getting a breadcrumb list.
The normal breadcrumb query is:
select
c.*,
nstB.lft,
nstB.rgt
from
categoryNST nstA
cross join categoryNST nstB
inner join category c on nstB.id = c.categoryId
where
nstA.lft between nstB.lft and nstB.rgt
and nstA.id = {categoryId}
order by
nstB.lft
The modified version that just returns the first node immediately below the root is:
select top 1 -- ONLY USE THE FIRST NODE
c.*,
nstB.lft,
nstB.rgt
from
categoryNST nstA
cross join categoryNST nstB
inner join category c on nstB.id = c.categoryId
where
nstA.lft between nstB.lft and nstB.rgt
and nstB.lft > 1 -- EXCLUDE THE ROOT NODE
and nstA.id = #categoryId#
order by
nstB.lft
Cheers,
Laker
I something like:
-root
--cat 1
---cat 1a
----cat 1b
---cat 1aa
----cat 1bb
--cat 2
--cat 3
---cat 3a
and I want to move cat2 to between root and cat 1. Should this be possible - I have made some changes to the downloaded templates which may be breaing something?
Yes, that's a valid move. I just tested using the nstreedemo app from the download and was able to make 'cat 2' the first child of 'root' (i.e. between root and cat 1)
The node I want to move is a direct child of the root node and I simply want to move it above the current first node as you mention. The current first child has several sub cats so not sure if that is an issue?
Thanks for your reply. I haven't been able to make the move as described. I've copied the showtree.cfm view from the download package over the modified version I was using and it still doesn't work. This is the only file I've changed so I'm not sure why it doesn't perform the move.
Any help appreciated.
In the demo app are you using the "Move To" drop down or the "Move Up/Move Down" links? The Move To drop down is for effectively changing the "parent" of a node, rather than for reordering nodes. The library supports what you are trying to do but the nstreedemo only shows a subset of functionality and achieves this via the move up/move down links.
If this is not what you are experiencing can you describe how you are trying to move the nodes?
Thanks
I'm trying to move the node using the Move Up/Move Down links and not the 'move to' dropdown. I'm using the nstree to drive the sections of my site (e.g. the main section navigation) and I want to add a 'home' node. Of course I want this to be the first child of the root so that it appears first in the main nav.
After I create the new node I can move it up once but then it won't move up again past the current first node (which has nested nodes itself)...! There are currently only three child nodes off the root.
Are you saying that the demo does or doesn't support what I am trying to do? I don't see why I shouldn't be able to move it up to the first child position (as you state that you can do it)...
I can post a link tomorrow to show you what is happening if that helps explain?
Thanks,
Adam