Nested Set Trees in ColdFusion

A new version of this project is available.

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.

See the references below for some good links that describe the Nested Set model in more detail (or just try a search in Google).

This entry provides a library of ColdFusion code that you may like to use to help manage your hierarchical data using the nested set model.

About the Code

This code is based on a PHP/mysql implementation by Rolf Brugger (thanks Rolf!), so have a quick read through that page before getting started.

This code is also based around creating an additional “partner” table that is used with an existing data table.

The library should work in ColdFusion 6.1 and above and any common database but I have only tested in MS SQL Server.

Download the Code

You can download this code from RIAForge

http://nstree.riaforge.org/

This download file contains:

NestedSetTree.cfc – the function library

nstree – directory containing a sample application

7 Comments

  1. Adam Cameron
    Posted May 31, 2008 at 9:32 am | Permalink

    What is your position on handling "race conditions" which could crop up by a subsequent call to – for example – moveToNextSibling() whilst a previous call to it is still running, so that the queries end up running like this:

    Request 1: updateLeft
    Request 2: updateLeft
    Request 1: updateRight
    Request 2: updateRight

    Rather then the required:

    Request 1: updateLeft
    Request 1: updateRight
    Request 2: updateLeft
    Request 2: updateRight

    Your tree data will become corrupted. I was caught out by this with my initial implementation of this sort of thing, in high-traffic situations.

    You might want to put some locking or transactionality around your queries.


    Adam

  2. Posted June 1, 2008 at 1:18 pm | Permalink

    Adam, thanks for raising this. I will look into adding locking. For the transaction handling I am considering leaving that for the calling code for now.

  3. Aaron Lee
    Posted June 5, 2008 at 8:25 pm | Permalink

    Very useful library. Thanks for sharing Kevan!

    Have you tried integrating this with Transfer ORM? I’d love to hear your thoughts on this.

    Aaron

  4. Posted June 6, 2008 at 2:22 pm | Permalink

    Hi Aaron

    Putting something together about integration with Transfer is a great idea. I will put up some thoughts about it shortly. Thanks for the suggestion.

  5. Aaron Lee
    Posted June 6, 2008 at 5:08 pm | Permalink

    Hi Kevan,

    That’s great! I will certainly look forward to it.

  6. Posted May 22, 2009 at 4:20 pm | Permalink

    This category tool is perfect for what I need in my e-comm store application.

    I have a dumb question. I have a typical product table. productid as PK.

    I need a product to be related to one or more categories and/or subcategories, any level deep.

    Would I simply create a linking table between my products table and category table? And use categoryId as a FK in my linking table? That way a product can be related to one or more items in the category table.

    Thank you so much!

    Will

  7. Posted May 22, 2009 at 4:50 pm | Permalink

    Hi Will

    Your plan sounds good to me.

    Let me know if you have any questions on getting up and running with the library.

    Regards

    Kevan