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

Finally we get to the endgame: formatting a tree-sorted query as an HTML list using ColdFusion. We’ll build upon what we’ve already gone through, of course. This time we’ll be building ListFromQueryTree, a UDF that takes a tree-sorted query and returns a correctly-nested HTML list.

It starts, of course, with a signature:

<cffunction name="ListFromQueryTree" returntype="string" output="No">
	<cfargument name="Query" type="query" required="Yes">
	<cfargument name="TitleColumn" type="string" required="No" default="Name">
	<cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
	<cfargument name="DepthPrefix" type="string" required="No" default="depth">
	<cfargument name="ListTag" type="string" required="No" default="ul">

Nothing in here should be surprising. We start with the query, then pass in the names of the columns that are the title and the depth information. We’ll also pass in a prefix to be used in our ul tags as CSS classes that help select sub-level lists of a certain depth. Lastly, we have the option of overriding the default list tag, ul, and using something else, such as ol or whatever you like.

Local variable declarations come next:

	<cfset var Ret="">
	<cfset var MinDepth=999>
	<cfset var Q=Arguments.Query>
	<cfset var LastDepth=0>
	<cfset var ThisDepth=0>
	<cfset var d=0>

Again, there are no surprises here. We’ll use d as a loop variable later on. Then we do our loop to find the minimum depth:

	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
		<cfif ThisDepth LT MinDepth>
			<cfset MinDepth=ThisDepth>
		</cfif>
	</cfloop>
	<cfset LastDepth=MinDepth-1>

We also set LastDepth to be one less that the minimum depth. Why?

Since we can’t cheat like we did with tables and just pad to the left the number of empty spaces, we’ll need to keep track of how deep we are in the list, so that we can then pop up or down the appropriate number of levels before and after each list item. In theory, we should never dig down more than one level at a time, but we may pop back up more than level at a time. If we’re on the last item of some deeply-nested list, once it ends we’ll need to close off the number of lists that are between that item and the next. By setting LastDepth to one less than the minimum, we know that we’ll always have cleanly-closed lists.

Then the main loop begins:

	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>

We’ll use ThisDepth as a shortcut for the depth of the current item. Using that longer version each time is just silly.

		<cfif LastDepth LT ThisDepth>
			<cfloop from="#IncrementValue(LastDepth)#" to="#ThisDepth#" index="d">
				<cfset Ret=Ret & '<#Arguments.ListTag# class="#Arguments.DepthPrefix##d#">'>
			</cfloop>
		<cfelse>
			<cfif LastDepth GT ThisDepth>
				<cfset Ret=Ret & RepeatString("</li></ul>",LastDepth-ThisDepth)>
			</cfif>
			<cfset Ret=Ret & "</li>">
		</cfif>

This is the meat of our function. We need to look at the depth of the current item as compared to the depth of the item before it. If it’s greater then we’ve dug down a bit and need to open a new list. Otherwise, it could be at the same level or upwards. If we’ve moved upwards, the last depth being greater than the current depth, then we need to close off the lists in between. Either way, we’ll also need to close off the previous item. In the first scenario, digging down, we didn’t close the current item, because it’ll need to be closed after all of its children are closed.

The rest is cake:

		<cfset Ret=Ret & "<li>" & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow])>
		<cfset LastDepth=ThisDepth>
	</cfloop>

We output the current item, but do not close it, as we know it will be closed in a later iteration of the loop. But what about the dangling items at the end of the loop, you ask?

	<cfif Q.RecordCount GT 0>
		<cfset Ret=Ret & RepeatString("</li></ul>",LastDepth-(MinDepth-1))>
	</cfif>
	<cfreturn Ret>
</cffunction>

We close them in one fell swoop at the end. Notice that we use MinDepth here, as that’s the depth we started with.

Calling our function is similar to the table version:

<cfoutput>#ListFromQueryTree(TreeStuff)#</cfoutput>

Yields:

  • Coding
    • Applications
      • C++
    • Hybrid
      • Perl
    • Web
      • ColdFusion
  • Food
    • Cheese

Again, I added a bit of color at the end by replacing the CSS classes with inline styles with ReplaceNoCase.

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.