Adjacency list in coldfusion

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开发者_Python百科 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?

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).

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) )>

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.

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#)#