– Common Function Library Project

queryTreeSort(stuff[, parentid][, itemid][, basedepth][, depthname])

Last updated March 28, 2006


Rick Osborne

Version: 1 | Requires: CF7 | Library: DataManipulationLib

Many datasets used in web programming are hierarchical, or parent-child. Each item may have multiple subordinate items associated with it, and those subordinate items may have their own subordinate items, ad infinitum. This sort of relationship is normally handled in a database by having a ParentID of one item point to the ID of another item. The tricky part is getting the data out of the database in a way such that you can display it logically. Most database engines do not offer native extensions for handling hierarchical data, so the easiest way is often to just have ColdFusion do the sorting.

Return Values:
Returns a query.


<cfquery datasource="#Request.DSN#" name="Stuff">
SELECT ItemID, Name, ParentID
FROM Stuff
<cfset TreeStuff=queryTreeSort(Stuff)>


Name Description Required
stuff Query to sort. Yes
parentid Column containing parent id. Defaults to parentid. No
itemid Column containing ID value. Defaults to itemid. No
basedepth Base depth of data. Defaults to 0. No
depthname Name for new column to use for depth. Defaults to TreeDepth. No

Full UDF Source:

 QueryTreeSort takes a query and efficiently (O(n)) resorts it hierarchically (parent-child), adding a Depth column that can then be used when displaying the data.
 @param stuff 	 Query to sort. (Required)
 @param parentid 	 Column containing parent id. Defaults to parentid. (Optional)
 @param itemid 	 Column containing ID value. Defaults to itemid. (Optional)
 @param basedepth 	 Base depth of data. Defaults to 0. (Optional)
 @param depthname 	 Name for new column to use for depth. Defaults to TreeDepth. (Optional)
 @return Returns a query. 
 @author Rick Osborne ( 
 @version 1, April 9, 2007 
<cffunction name="queryTreeSort" returntype="query" output="No">
	<cfargument name="Stuff" type="query" required="Yes">
	<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">
	<cfset var RowFromID=StructNew()>
	<cfset var ChildrenFromID=StructNew()>
	<cfset var RootItems=ArrayNew(1)>
	<cfset var Depth=ArrayNew(1)>
	<cfset var ThisID=0>
	<cfset var ThisDepth=0>
	<cfset var RowID=0>
	<cfset var ChildrenIDs="">
	<cfset var ColName="">
	<cfset var Ret=QueryNew(ListAppend(Stuff.ColumnList,Arguments.DepthName))>
	<!--- Set up all of our indexing --->
	<cfloop query="Stuff">
		<cfset RowFromID[Stuff[Arguments.ItemID][Stuff.CurrentRow]]=CurrentRow>
		<cfif NOT StructKeyExists(ChildrenFromID, Stuff[Arguments.ParentID][Stuff.CurrentRow])>
			<cfset ChildrenFromID[Stuff[Arguments.ParentID][Stuff.CurrentRow]]=ArrayNew(1)>
		<cfset ArrayAppend(ChildrenFromID[Stuff[Arguments.ParentID][Stuff.CurrentRow]], Stuff[Arguments.ItemID][Stuff.CurrentRow])>
	<!--- Find parents without rows --->
	<cfloop query="Stuff">
		<cfif NOT StructKeyExists(RowFromID, Stuff[Arguments.ParentID][Stuff.CurrentRow])>
			<cfset ArrayAppend(RootItems, Stuff[Arguments.ItemID][Stuff.CurrentRow])>
			<cfset ArrayAppend(Depth, Arguments.BaseDepth)>
	<!--- Do the deed --->
	<cfloop condition="ArrayLen(RootItems) GT 0">
		<cfset ThisID=RootItems[1]>
		<cfset ArrayDeleteAt(RootItems, 1)>
		<cfset ThisDepth=Depth[1]>
		<cfset ArrayDeleteAt(Depth, 1)>
		<cfif StructKeyExists(RowFromID, ThisID)>
			<!--- Add this row to the query --->
			<cfset RowID=RowFromID[ThisID]>
			<cfset QueryAddRow(Ret)>
			<cfset QuerySetCell(Ret, Arguments.DepthName, ThisDepth)>
			<cfloop list="#Stuff.ColumnList#" index="ColName">
				<cfset QuerySetCell(Ret, ColName, Stuff[ColName][RowID])>
		<cfif StructKeyExists(ChildrenFromID, ThisID)>
			<!--- Push children into the stack --->
			<cfset ChildrenIDs=ChildrenFromID[ThisID]>
			<cfloop from="#ArrayLen(ChildrenIDs)#" to="1" step="-1" index="i">
				<cfset ArrayPrepend(RootItems, ChildrenIDs[i])>
				<cfset ArrayPrepend(Depth, ThisDepth + 1)>
	<cfreturn Ret>
blog comments powered by Disqus


Latest Additions

Raymond Camden added
April 25, 2016

Chris Wigginton added
January 18, 2016

Gary Stanton added
November 19, 2015

Sebastiaan Naafs - van Dijk added
November 13, 2015

Simon Bingham added
April 15, 2015

Created by Raymond Camden / Design by Justin Johnson