Parent-Child Queries Enhancement: Stacked Numbering

Mathijs Gaalman requested:

I need to have an automated numbering in front of the node names, like this:
1. Hello
  1.1 Yo
  1.2 What's up
    1.2.1 good day
2. Later
  2.1. ciaa
    2.1.1 arrivideci

No problem. I can name that tune in a dozen and a half lines of code.

Pause for a second and think about what it is that we need to do. Basically, we need a running list of where we are in the tree. We need the list to expand as the depth increases, and contract as the depth decreases. It seems to me that this would be best solved with an array, with one slot for each depth level we’ve passed through. We can expand and contract the array as necessary, and we can easily convert an array to a dotted list. We’ll essentially be creating a poor-man’s stack, pushing and popping counters as we go.

<cfset Q=QueryTreeSort(Addresses, "Parent_Number", "Address_Book_Number", 1)>
<cfset Numbers=ArrayNew(1)>
<cfset Numbers[1]=0>
<cfloop query="Q">
	<cfif Q.TreeDepth GT ArrayLen(Numbers)>
		<!--- Expand our array --->
		<cfloop from="#IncrementValue(ArrayLen(Numbers))#" to="#Q.TreeDepth#" index="i">
			<cfset Numbers[i]=0>
		</cfloop>
	<cfelseif ArrayLen(Numbers) GT Q.TreeDepth>
		<!--- Collapse our array --->
		<cfloop from="#ArrayLen(Numbers)#" to="#IncrementValue(Q.TreeDepth)#" index="i" step="-1">
			<cfset ArrayDeleteAt(Numbers,i)>
		</cfloop>
	</cfif>
	<cfset Numbers[Q.TreeDepth]=Numbers[Q.TreeDepth]+1>
	<cfoutput>#ArrayToList(Numbers,".")#. #HTMLEditFormat(Q.Alpha_Name)#<br /></cfoutput>
</cfloop>

We start off by tree-sorting our query, making sure to set our base depth (the fourth parameter) to 1 instead of 0. We do this because arrays in ColdFusion start at 1 and this will make our lives much easier. We then create and initialize our array, and begin looping over our query.

The cfif block handles the expanding and contracting we talked about before. If the depth of the current item is more than the number of items in our array, we’ll need to expand it. To do that, we just loop until we have the correct number of elements, initializing the elements as we go. (In theory, we’ll never drop down more than one level at a time, so we don’t really need a loop, but it’s better not to make that kind of assumption if we don’t have to.) Conversely, if we have more elements in our array than we have depth levels, we need to chop them off the end, looping backwards to do this. You’ll notice that if the depth and the array are the same size, we do nothing.

After our expansion and contraction, the only thing left to do is increment the last number in the array. (Hence why we always initialized new elements to 0, knowing that we’d just increment them to 1 before we output them.)

As for the output, the built-in ColdFusion function ArrayToList allows us to cheat and will do the conversion for us by putting dots in between each of the numbers.

Running this against our initial test data should yield:

1. Coding
1.1. Applications
1.1.1. C++
1.2. Hybrid
1.2.1. Perl
1.3. Web
1.3.1. ColdFusion
2. Food
2.1. Cheese

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.