Writing a Parent-Child Query Sorter in CF, Part 11

This entry may be a bit anticlimactic after the last one in this series, but it still ought to be mentioned.

How can we quickly and easily output only the direct children, not all of the descendants, for a given item?

First, the question is a bit of a trick. We could do it with the query that we have thus far, but it would be a bit of a hack:

  1. Given: A tree-sorted query and the ID of the current item,
  2. Iterate through the query from the row of the given item,
  3. Looking for items with a depth that is one greater than the depth of the given item,
  4. Repeating until we hit an item with depth equal or less than that of the given item.

That approach is a bit simplistic, and it suffers from the same problems as last time. It’s not something that you want to be doing over and over, and really, it’s redundant because our sort algorithm already spends time finding children. Do you see where this is going?

Our best bet is to just add another column to our sorted query that contains the children.

Like last time, we want to allow the programmer the ability to tell us to not return the children if they know they’ll never use them:

    <cfargument name="ChildrenRowName" type="string" required="No" default="TreeChildrenRows">

We’re going to need a new local variable:

    <cfset var ThisParentRowID="">

Further down, we need to add the new column to our result query:

    <cfif Arguments.ChildrenRowName NEQ ""><cfset RetColList=ListAppend(RetColList, Arguments.ChildrenRowName)></cfif>

In our main loop we’ll need to set the correct cell. Update this block in the inner loop:

            <!--- Add the parent if there is one --->
            <cfif StructKeyExists(NewRowFromID, Stuff[Arguments.ParentID][RowID])>
                <cfset ThisParentRowID=NewRowFromID[Stuff[Arguments.ParentID][RowID]]>
                <cfset ThisLineage=ListAppend(ThisLineage, ThisParentRowID)>
                <cfif Arguments.ChildrenRowName NEQ "">
                    <cfset QuerySetCell(Ret, Arguments.ChildrenRowName, ListAppend(Ret[Arguments.ChildrenRowName][ThisParentRowID],Ret.RecordCount), ThisParentRowID)>

That’s it. That’s all there is to it.

Did you catch what we did in that last part? The QuerySetCell call isn’t setting the cell in the current row, like all of the other QuerySetCell calls do. This one sets the new cell in the row of the parent by appending the current row number to it. That is, as we encounter children, we update the parents’ lists.

Our test query has now bloated to this:

# ID Name Parent Depth Lineage Children
1 12 Coding 0 0   2,4,6
2 15     Applications 12 1 1 3
3 8         C++ 15 2 1,2  
4 9     Hybrid 12 1 1 5
5 19         Perl 9 2 1,4  
6 26     Web 12 1 1 7
7 16         ColdFusion 26 2 1,6  
8 7 Food 0 0   9
9 1     Cheese 7 1 8  

Outputting a list of children becomes similarly easy:

<cfset ChildRowID=1>
<cfset ChildrenRowIDs=TreeStuff.TreeChildrenRows[ChildRowID]>
<cfif ChildrenRowIDs NEQ "">
    <cfoutput><p><strong>See also:</strong></p></cfoutput>
    <cfset SeeAlso=ArrayNew(1)>
    <cfloop list="#ChildrenRowIDs#" index="i">
        <!--- Do any formatting you want here --->
        <cfset ArrayAppend(SeeAlso,TreeStuff.Name[i] & " (" & ListLen(TreeStuff.TreeChildrenRows[i]) & ")")>
    <cfoutput><ul><li>#ArrayToList(SeeAlso, "</li><li>")#</li></ul></cfoutput>


See also:

  • Applications (1)
  • Hybrid (1)
  • Web (1)

As an exercise for the reader, I’ll leave it up to you to figure out how to add child counts to rows instead of using the ListLen hack that I’ve shown above. It’s very, very similar to what we’ve done here.

By Rick Osborne

I am a web geek who has been doing this sort of thing entirely too long. I rant, I muse, I whine. That is, I am not at all atypical for my breed.