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

In this installment we’re going to write a function to turn our tree-sorted query into an HTML table. A function may not be the best place to do this, as your presentation is very likely tied to your data. However, for simplicity let’s assume that you just want a function to dump the data in a table-based tree-like format, and you can take that function and tweak it to your specific needs.

Let’s dive right in with our function signature, shall we?

<cffunction name="TableFromQueryTree" returntype="string" output="No">
	<cfargument name="Query" type="query" required="Yes">
	<cfargument name="Columns" type="array" required="Yes">
	<cfargument name="Titles" type="array" required="Yes">
	<cfargument name="Classes" type="array" required="No">
	<cfargument name="SpannedColumn" type="string" required="No">
	<cfargument name="ParentID" type="string" required="No" default="ParentID">
	<cfargument name="ItemID" type="string" required="No" default="ItemID">
	<cfargument name="BaseDepth" type="numeric" required="No" default="0">
	<cfargument name="DepthName" type="string" required="No" default="TreeDepth">
	<cfargument name="EvenOdd" type="array" required="No" default="#ListToArray('even,odd')#">

Many of these arguments are the same as last time. First, we pass in the query to be displayed. The next 3 arguments are arrays that tell the function which columns to display and in what order, what to use for the column titles, and what classes to assign to the cells for each of the columns. This is a half-hearted attempt to abstract the function a little bit from the presentation details. The CSS classes will come in handy later for a bit of post-processing.

The SpannedColumn argument is the name of the column that will have the tree formatting assigned to it, and will be pushed to the right depending on the depth of the row. The rest are copied straight from the previous function up to EvenOdd. I prefer my tables to be zebra-striped, so passing in the class names that you use for even and odd rows will save time later.

Next, we declare our local variables:

	<cfset var MinDepth=999>
	<cfset var MaxDepth=0>
	<cfset var Ret="">
	<cfset var Levels=0>
	<cfset var Q=Arguments.Query>
	<cfset var ColID=0>

Because we can start the tree depth with any level, we’ll need to calculate the minimum and maximum depths in the query so that we can tell what the range of depths are. Since we’ll be using the passed-in query so much, I found the code easier to read when I copied a reference of it to Q. We’ll use ColID as a loop variable.

	<cfloop query="Q">
		<cfif Q[Arguments.DepthName][Q.CurrentRow] GT MaxDepth>
			<cfset MaxDepth=Q[Arguments.DepthName][Q.CurrentRow]>
		</cfif>
		<cfif Q[Arguments.DepthName][Q.CurrentRow] LT MinDepth>
			<cfset MinDepth=Q[Arguments.DepthName][Q.CurrentRow]>
		</cfif>
	</cfloop>
	<cfset Levels=MaxDepth-MinDepth+1>

The table header is fairly straigtforward:

	<cfsavecontent variable="Ret">
		<cfoutput>
<table>
	<thead>
		<tr>
			<cfloop from="1" to="#ArrayLen(Columns)#" index="ColID">
			<th<cfif Classes[ColID] NEQ ""> class="#Classes[ColID]#"</cfif><cfif (Columns[ColID] EQ Arguments.SpannedColumn) AND (Levels GT 1)> colspan="#Levels#"</cfif>><cfif Titles[ColID] NEQ "">#Titles[ColID]#<cfelse>#Columns[ColID]#</cfif></th>
			</cfloop>
		</tr>
	</thead>

We’re going to pass back a string instead of outputting directly, hence the cfsavecontent tag. We loop over the column array that was passed in and output any class information that we might have been given. That next cfif block sets up the span if the current column is our tree column and is supposed to be spanned. Last, we try to output a column title if one was given, but output the SQL column name if not.

The body is a bit more convoluted, but not too much worse:

	<tbody>
			<cfloop query="Q">
	<tr class="#Arguments.EvenOdd[IncrementValue(Q.CurrentRow MOD 2)]#">
				<cfloop from="1" to="#ArrayLen(Columns)#" index="ColID">
		<cfif (Columns[ColID] EQ Arguments.SpannedColumn)><cfloop from="#IncrementValue(MinDepth)#" to="#Q[Arguments.DepthName][Q.CurrentRow]#" index="i"><td>   </td></cfloop></cfif>
		<td<cfif Classes[ColID] NEQ ""> class="#Classes[ColID]#"</cfif><cfif (Columns[ColID] EQ Arguments.SpannedColumn) AND (Int(MaxDepth - Q[Arguments.DepthName][Q.CurrentRow]) GT 0)> colspan="#IncrementValue(MaxDepth-Q[Arguments.DepthName][Q.CurrentRow])#"</cfif>>#HTMLEditFormat(Q[Columns[ColID]][Q.CurrentRow])#</td>
				</cfloop>
	</tr>
			</cfloop>
	</tbody>

Of course, we start by looping over the query, then again over each column. In between we have that even/odd magic that I mentioned earlier. The next cfif block inside the inner loop is essentially the same as before: if we’re looking at the tree-spanned column and the current depth is greater than the minimum depth, then output as many padding columns as needed. The next cfif block is the same class-generating stuff we saw before.

The third cfif block is a bit less obvious. Yet again, we’re only interested if the current column is the spanned one. If so, and the current depth is less than the maximum, then we need to generate a colspan attribute to make sure this cell fills all the space it needs to. After that, output the data and close off the loops.

The rest of the function just closes out all of the HTML, loops, etc, that need to be closed, and returns our data.

</table>
		</cfoutput>
	</cfsavecontent>
	<cfreturn Ret>
</cffunction>

That’s it. We now have a function to dump tree-based tables. Let’s call it:

<cfoutput>#
TableFromQueryTree(
	TreeStuff,
	ListToArray("ItemID,Name,ParentID,TreeDepth"),
	ListToArray("ID,Name,Parent,Depth"),
	ListToArray("num,string,num,num"),
	"Name"
)#</cfoutput>

Vis:

ID Name Parent Depth
12 Coding 0 0
15     Applications 12 1
8        C++ 15 2
9     Hybrid 12 1
19        Perl 9 2
26     Web 12 1
16        ColdFusion 26 2
7 Food 0 0
1     Cheese 7 1

Of course, I sprinkled in some extra formatting after the function returned by creative use of ReplaceNoCase and the class attributes that I provided.

After all of the acrobatics to get padded tables, you would think that doing a correctly-nested HTML list would be cake, right? Wrong. Nested lists are actually a bit trickier, and I’ll be going over them next time.

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