Adjacency list in coldfusion

Question

This image demonstrates what I'm trying to achieve. On the left is the table data, on the right the table I'm trying to create.

the table uses the parentID to reference another item within the same table to create a hierarchy.

How would I go about creating queries and organizing them in this way?

What if I select "Pirates" and want to get all the parents? Is there anything in CFML that makes this easier than looping queries until I get to the top?

enter image description here

As long as you are using the adjacency list model, you must loop. But ultimately it is more about choosing the right model to represent your hierarchy data. The links in orangepips answer provide a great summary of the available options (nested sets, etc... ) as well as their pros and cons. — Leigh, Jun 23 '11 at 00:10
(note: CTE's make life easier, but under the hood you are still looping ) — Leigh, Jun 23 '11 at 00:34

score 2 · Answer 1 · edited May 23 '17 at 12:01

Your hierarchical data is represented using a method known as adjacency list. There are a number of other options, which you ultimately use depends on how frequently that data changes and what underlying database you're using - where some have features that facilitate querying adjacency list - chief among them are Common Table Expressions (CTE). See the question linked for ways of doing it with other databases.

Using a CTE you can retrieve a hierarchy from an adjacency list in order and calculate the "level" of each entry - in your case to indent correctly. If your database does not have CTEs and there's not another way of querying it easily, consider using a different approach, nested sets are probably the most accessible, where retrieval is fast but at the cost of more complex change algorithms (i.e. insert, delete, move).

score 1 · Answer 2 · answered Jun 22 '11 at 23:10

I haven't tested this... :)

<!--- item.cfc --->
<cfcomponent persistent="true" cache="read-only">
    <cfproperty name="id" fieldtype="id">
    <cfproperty name="parent"
                fieldtype="many-to-one" cfc="item" fkcolumn="ParentID">
    <cfproperty name="children" type="array"
                fieldtype="one-to-many" cfc="item" fkcolumn="ParentID" inverse="true">
</cfcomponent>

<!--- display.cfm --->
<cffunction name="printItem" output="true">
    <cfargument name="item" required="true">
    <table>
        <tr>
        <td>#item.getName()#

        <cfif item.hasChildren()>
            <table>
              <cfloop array="#item.getChildren()#" index="local.i">
                  <tr>
                      <td>#printItem(local.i)#
              </cfloop>
            </table>
        </cfif>
    </table>
</cffunction>

<cfset printItem( entityLoadByPK("item",1) )>

score 0 · Answer 3 · answered Jun 23 '11 at 18:07

One approach that I've considered when contemplating a similar need is to build the hierarchy asynchronously - that is, with AJAX requests. Depending on your need, this may or may not work for you, but imagine that instead of building the whole tree immediately, you only present the user with the top level initially. Then, when the user selects one of the top level items, an AJAX request is made to find the children of the selected item. Repeat as needed for each child to build the tree. Done this way, the problem is very simple and the queries and code used to implement it are also very simple.

cftree might not be a bad idea for prototyping this idea! :) — Henry, Jun 23 '11 at 18:45

Daniel · Accepted Answer · 2011-06-23T22:20:17.980

0

This is what I've come up with with your help.

<!--- adjacency list display --->
<cffunction name="adjacentList" output="true">
    <cfargument name="alQuery" required="true">
    <cfargument name="qid" required="false" default="0">

        <cfquery name="alSubQuery" dbtype="query">
            SELECT * FROM alQuery WHERE parentID=#qid#
        </cfquery>

        <cfif alSubQuery.RecordCount neq 0>
            <ul>
            <cfloop query="alSubQuery">
                <li>
                #name#
                #adjacentList(#alQuery#,#id#)#
                </li>
            </cfloop>
            </ul>
        </cfif>
</cffunction>

using: #adjacentList(#query#)#

edited Jun 23 '11 at 22:20

answered Jun 23 '11 at 21:46

Daniel

34,125
17
102
150

mySQL for testing, but I will use msSQL, so I'm using the most basic sql functionality to make sure both work. – Daniel Jun 23 '11 at 22:57
1

You might consider using MS SQL for testing too. So you will have a better idea of the actual results and any issues you might encounter. Plus you will be able to experiment with CTE's.. (Side note: be sure to `var` scope all of your function local variables) – Leigh Jun 23 '11 at 23:04

Adjacency list in coldfusion

4 Answers4