Managing Trees Using TreeManager.cfc and Flash Forms
Posted on Nov 04, 2005
This is part of my continuing Open-Source Review. The support I received regarding the cf open-source list was outstanding. The other aspect of the open-source review was to create articles covering some of the open source projects on that list. These articles will cover: 1) What is the intent of the project; what does it do? 2) How do you implement the project? 3) Where to get more help/contribute?
The first project I will cover is Barney Boisvert's TreeManager.cfc. In case you are wondering, the reason this is first is simply a matter of that I was already building an application that uses it. TreeManager.cfc can be downloaded at: http://www.barneyb.com/web/static/treemanager.zip.What is TreeManager.cfc
Essentially, TreeManager.cfc encapsulates all the functions you would need to manage a tree using a the nested set model. The (very) brief explanation of a nested set tree is that rather than using a self-referential parent/parentid column to determine parent-child relationships (i.e. the adjecency list model), it uses a combination of left and right positions. This has the benefit of enormously simplifying the process of pulling tree hierarchies from the database without the need for a recursive query (or cfloop-ing over queries). TreeManager.cfc was written to support ColdFusion MX 6.1 and MySQL 4.1.
More Information on Nested Sets
Back in 2003, I wrote an article for CFDJ regarding the nested set model for building trees in a flat database table (which, btw CFDJ, you still have that attributed to some Ryan Rinaldi of Chicago - not cool!). Much of that code is out of date, but it offers some info on what a nested set tree is. Barney Boisvert covers this topic in better detail on his blog. Most of the information I gathered on this topic comes from Joe Celko's SQL for Smarties.
Building a Tree Manager Tool
Modifying the Component
First things first, for my project, I needed to support Microsoft SQL Server 2000, so there are some changes that needed to be made to TreeManager.cfc first. The primary change is to convert all LIMIT 1 to SELECT TOP 1. This is easy, however, in one case (the sortChildren method), it uses the offset value that is offered in MYSQL, and I have posted the solution previously here. I have also changed modified the component to use a GUID for the ID column.
<cfquery datasource="#variables.my.dsn#" name="getCurrentItem">
SELECT TOP 1 #variables.id# AS id
FROM #table#
WHERE #variables.id# IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#getChildIds(parentID)#" list="true" />)
AND id NOT IN (SELECT TOP #i - 1# #variables.id# FROM #table#)
ORDER BY #lpos#
</cfquery>
Also, I wanted getNode to return customized set of columns much list the getNodeList function, so I modifed this code as well:
<cffunction name="getNode" access="public" output="false" returntype="struct" hint="Returns a struct with the id, lpos, and rpos of the specified node" throws="IllegalStateException.NoSuchNodeException">
<cfargument name="id" type="string" required="true" hint="The id of the node to get info about." />
<cfargument name="columnList" type="string" required="false" hint="The list of columns to select for the recordset. This value will be injected directly into the SQL, so it must be sanitized before passing. Since the string is injected into the SELECT clause of the statement, you cannot perform joins, but you can use subqueries. The statement you're injecting into is a single table SELECT on the unaliased table name, and ordered by the lpos column." />
<cfset var get = "" />
<cfset var result = structNew() />
<cfset var i = 0>
<cfquery datasource="#variables.my.dsn#" name="get">
SELECT #variables.id# AS id, #rpos# AS rpos, #lpos# AS lpos <cfif isDefined("arguments.columnList")>, #arguments.columnList#</cfif>
FROM #table#
WHERE #variables.id# = <cfqueryparam cfsqltype="cf_sql_idstamp" value="#id#" />
</cfquery>
<cfif NOT get.recordCount EQ 1>
<cfthrow type="IllegalStateException.NoSuchNodeException" message="The node you specified doesn't exit" />
</cfif>
<cfset result.id = get.id />
<cfset result.lpos = get.lpos />
<cfset result.rpos = get.rpos />
<cfif isDefined("arguments.columnList")>
<cfloop from="1" to="#listLen(arguments.columnList)#" index="i">
<cfset result[#listGetAt(arguments.columnList,i)#] = get[#listGetAt(arguments.columnList,i)#]>
</cfloop>
</cfif>
<cfreturn result />
</cffunction>
Lastly, for reasons you will see in my form, I changed the getParent method from private to public.
Creating the Table
My MS SQL table includes simply a categoryid (GUID), title_nav, title_full, lpos, rpos and sortOrder. TreeManager.cfc requires the id, lpos, rpos and sort fields at a minimum. The title_nav im this case is because I am using my tree to build content categories and title_nav is a shortened title that can appear on the site navigation. Title_full should be self-explanatory at this point. In my zip, I have included a sql script to create the table. You will note that it inserts a root node by default into the table; my form will disallow modifying or deleting the root node. Here the code:
[categoryid] [uniqueidentifier] NOT NULL ,
[title_nav] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[title_full] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lpos] [int] NULL ,
[rpos] [int] NULL ,
[sortOrder] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
(
[categoryid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblCategories_categoryid] DEFAULT (newid()) FOR [categoryid]
GO
INSERT INTO [dbo].[tblCategories](title_nav,lpos,rpos)
VALUES ('root',1,2)
GO
Building the Form
It is easy to become overly enamored of the new flash forms, however, this was a case where it seemed more intuitive to be able to place all the functionality on a single form and even do it without page reloads using remoting. (One note: the form and remoting wrapper component to be discussed later are not complete; delete, moveUp and moveDown are functionality I intend to add) The form is essentially made up of a cftree on the left and a simple form on the right. You will note some actionscript functions, which we will cover shortly. Keep in mind that this form and the component discussed later are still in development (please report any bugs if - I mean when - you find them) The code for the form:
objTreemanager = createObject("component","opensourcereview.treemanager.components.treemanager.treemanager").init("opensourcereview","tblCategories","sortOrder","lpos","rpos","categoryID");
qryNodeList = objTreemanager.getNodeList('categoryid,title_nav,lpos');
parent = "";
</cfscript>
<cfoutput>
<cfform format="flash" name="theForm" onsubmit="{return save()}">
<cfformitem type="script">
function clearForm() {
// this gets an id to prefill the form var connection:mx.remoting.Connection = mx.remoting.NetServices.createGatewayConnection("http://localhost/flashservices/gateway/");
var myService:mx.remoting.NetServiceProxy;
var responseHandler = {};
var categoryID = categoryID;
var parentid = parentid;
responseHandler.onResult = function(results:String):Void {
categoryID = results;
}
responseHandler.onStatus = function( stat: Object ):Void {
alert("Error while calling cfc:" + stat.description);
}
_root.resetForm();
parentid.selectedIndex = 0;
myService = connection.getService("opensourcereview.treemanager.components.remoting.categories", responseHandler);
myService.getguid();
}
function getCategories() {
var connection:mx.remoting.Connection = mx.remoting.NetServices.createGatewayConnection("http://localhost/flashservices/gateway/");
var myService:mx.remoting.NetServiceProxy;
var responseHandler = {};
var lstCategories = lstCategories;
responseHandler.onResult = function(results:Object):Void {
lstCategories.dataProvider = results;
}
responseHandler.onStatus = function( stat: Object ):Void {
alert("Error while calling cfc:" + stat.description);
}
myService = connection.getService("opensourcereview.treemanager.components.remoting.categories", responseHandler);
myService.getTreeXML();
}
function setParent() {
// if the data was not loaded via xml if (lstCategories.selectedNode.getProperty('data').value != null) {
lstCategories.selectedNode.setProperty('id',lstCategories.selectedNode.getProperty('data').value)
}
if (lstCategories.selectedNode.getProperty('id') != null) {
for (var i = 0; i < parentid.length; i++){
if (parentid.getItemAt(i).data == lstCategories.selectedNode.getProperty('id')){
parentid.selectedIndex = i;
break;
}
}
}
}
function setEdit() {
// if the data was not loaded via xml if (lstCategories.selectedNode.getProperty('data').value != null) {
lstCategories.selectedNode.setProperty('id',lstCategories.selectedNode.getProperty('data').value)
}
if (lstCategories.selectedNode.getProperty('id') != null) {
var connection:mx.remoting.Connection = mx.remoting.NetServices.createGatewayConnection("http://localhost/flashservices/gateway/");
var myService:mx.remoting.NetServiceProxy;
var responseHandler = {};
var categoryID = categoryID;
var title_nav = title_nav;
var title_full = title_full;
var parentid = parentid;
var submitted = submitted;
responseHandler.onResult = function(results:Object):Void {
categoryID.text = results.categoryid;
title_nav.text = results.title_nav;
title_full.text = results.title_full;
for (var i = 0; i < parentid.length; i++){
if (parentid.getItemAt(i).data == results.parentid){
parentid.selectedIndex = i;
break;
}
}
submitted.label = 'Edit Category';
}
responseHandler.onStatus = function( stat: Object ):Void {
alert("Error while calling cfc:" + stat.description);
}
myService = connection.getService("opensourcereview.treemanager.components.remoting.categories", responseHandler);
myService.getNode(lstCategories.selectedNode.getProperty('id'));
}
}
function save() {
var connection:mx.remoting.Connection = mx.remoting.NetServices.createGatewayConnection("http://localhost/flashservices/gateway/");
var myService:mx.remoting.NetServiceProxy;
var responseHandler = {};
var lstCategories = lstCategories;
var categoryID = categoryID;
var title_nav = title_nav;
var title_full = title_full;
var parentid = parentid;
var submitted = submitted;
var getCategories:Function = getCategories;
responseHandler.onResult = function(results:Object):Void {
_root.getCategories();
parentid.labelField = "label";
parentid.dataProvider = results;
_root.clearForm();
alert("Your category has been saved.");
}
responseHandler.onStatus = function( stat: Object ):Void {
alert("Error while calling cfc:" + stat.description);
}
myService = connection.getService("opensourcereview.treemanager.components.remoting.categories", responseHandler);
myService.save(categoryID.text,title_nav.text,title_full.text,parentid.selectedItem.data,'opensourcereview');
return false;
}
</cfformitem>
<cfformgroup type="hbox">
<cfformgroup type="panel" label="Edit Category">
<cftree name="lstCategories">
<cfloop query="qryNodeList">
<!---
the getParent method was changed from package to public
getParent throws an error if the category has no parent (i.e. the parent is the root of our tree)
--->
<cftry>
<cfset parent = objTreeManager.getParent(qryNodeList.categoryID).id>
<cfcatch>
<cfset parent = "root">
</cfcatch>
</cftry>
<cfif qryNodeList.lpos EQ 1>
<cfset expand = true>
<cfelse>
<cfset expand = false>
</cfif>
<cftreeitem value="#qryNodeList.categoryID#" display="#qryNodeList.title_nav#" parent="#parent#" expand="#expand#" />
</cfloop>
</cftree>
<cfformgroup type="horizontal">
<cfinput type="button" name="Edit" value="Edit" onclick="{setEdit();}" />
<cfinput type="button" name="SetAsParent" value="Set As Parent" onclick="{setParent();}" />
<cfinput type="button" name="ClearForm" value="Clear Form" onclick="{clearForm();}" />
</cfformgroup>
</cfformgroup>
<cfformgroup type="panel" id="formPanel" label="Category Form">
<cfinput type="text" name="title_nav" label="Title (nav):" required="true" validate="noblanks" maxlength="50" />
<cfinput type="text" name="title_full" label="Title (full):" maxlength="255" />
<cfselect name="parentid" label="Parent Category:">
<cfloop query="qryNodeList">
<option value="#qryNodeList.categoryID#">#repeatString("-",qryNodeList.depth-1)# #qryNodeList.title_nav#</option>
</cfloop>
</cfselect>
<cfinput type="submit" name="submitted" value="Add Category" />
<cfinput type="text" name="categoryID" value="#insert("-", createUUID(), 23)#" visible="false" />
</cfformgroup>
</cfformgroup>
</cfform>
</cfoutput>
The guts of the form are fairly simple. Most of the functionality is in the actionScript.
The function clearForm simply resets the form, but also retreives a GUID to prefill the hidden id form field.
Function getCategories is used to repopulate the tree when after a category has been added or edited. It uses a method to fill the tree that was detailed at ASFusion. We will cover the getTreeXML component function later on.
Function setParent is used to set the parentid select box value with the selected item on the tree. It is fairly straightforward, but here, as in the upcoming setEdit function, I need to check how the tree was populated. We initially load it using the standard cftreeitem method, but, as discussed above, we often repopulate it using xml. For some reason, you refer to the values differently in either case.
Function setEdit gets the full record data of the selected tree node and prepopulates the form with the values to allow you to edit the item.
The purpose of function save should be obvious. After saving it reloads the tree with the updated data and clears the form. One thing I don't like is that when the tree is reloaded, it is initially closed. My wish list would include having this open to the added/edited item.
Building the Remoting Component
My categories component is essentially intended to wrap some of the functionality of the TreeManager.cfc and make it available to remoting. For instance, the getNode function simply opens up the getNode of the treeManager to remoting:
<cfargument name="categoryid" type="string" required="true">
<cfscript>
var objTreemanager = createObject("component","opensourcereview.treemanager.components.treemanager.treemanager").init("opensourcereview","tblCategories","sortOrder","lpos","rpos","categoryID");
var stReturn = objTreemanager.getNode(arguments.categoryid,'categoryid,title_nav,title_full');
// inject the parent id try {
stReturn["parentid"] = objTreemanager.getParent(arguments.categoryid).id;
}
catch(Any excpt) {
stReturn["parentid"] = "";
}
// this is a hack because id isn't seen in actionScript for some reason stReturn["categoryid"] = stReturn.id;
return stReturn;
</cfscript>
</cffunction>
The getTreeXML is intended to take the query returned by TreeManager's getNodeList function and turn it into an xml format that is needed to populate the cftree using the dataProvider method. Note: I believe I fixed issues with writing the xml, but this is where I have had the most complications.
<!--- create a new category tree to send back to update the form --->
<cfset var objTreemanager = createObject("component","opensourcereview.treemanager.components.treemanager.treemanager").init("opensourcereview","tblCategories","sortOrder","lpos","rpos","categoryID")>
<cfset var xmlReturn = "">
<cfset var qryCategories = objTreemanager.getNodeList('categoryid,title_nav,lpos')>
<cfoutput>
<cfxml variable="xmlReturn"><categories id="#qryCategories.categoryid[1]#" label="#qryCategories.title_nav[1]#"><cfloop query="qryCategories" startrow="2"><category id="#qryCategories.categoryid#" label="#xmlFormat(qryCategories.title_nav)#"><cfif (qryCategories.currentRow EQ qryCategories.recordCount)><cfloop from="#qryCategories.depth-1#" to="1" index="i" step="-1"></category></cfloop><cfelseif (qryCategories.depth[currentrow+1] LTE qryCategories.depth) AND (qryCategories.currentRow NEQ qryCategories.recordCount)><cfset count=qryCategories.depth-qryCategories.depth[currentrow+1]+1><cfloop from="1" to="#count#" index="i"></category></cfloop></cfif></cfloop></categories></cfxml>
</cfoutput>
<cfreturn xmlReturn>
</cffunction>
The getGUID function is the most basic, simply returning a new GUID is MS SQL format.
<cfreturn insert("-", createUUID(), 23)>
</cffunction>
Most of the logic in this component occurs in the save method. This is because it does a number of things. First, it uses the TreeManager getNode method to determine whether you are adding a new category or editing an existing one. In the case it is adding, it includes an add query to insert the node without any positioning, then it uses TreeManager's injectIntoHierarchy method to place the newly inserted node under its proper parent (the injectIntoHierarchy manages updating the lpos and rpos of the new category and shifting any others to accomodate it). In the case of edit, after determining that you are not attempting to edit the root node, we perform a query to update the title_nav and title_full with the new data. Then we check to see whether you have changed the parent of the node. If you have, we need to remove the node from the hierarchy (using removeFromHierarchy which will manage updating any lpos and rpos for affected nodes) and then we reinject it into the heierarchy with its new parent. Lastly, we assemple a query to return that is used to update the cfselect in the flash form with the newly updated categories.
<cfargument name="categoryid" type="string" required="true">
<cfargument name="title_nav" type="string" required="true">
<cfargument name="title_full" type="string" required="false" default="">
<cfargument name="parentid" type="string" required="true">
<cfargument name="dsn" type="string" required="true">
<cfscript>
var objTreemanager = createObject("component","opensourcereview.treemanager.components.treemanager.treemanager").init("opensourcereview","tblCategories","sortOrder","lpos","rpos","categoryID");
var stExisting = structNew();
var stExistingParent = "";
var stReturn = "";
var qryCategories = "";
var insCategory = "";
var updCategory = "";
var tmpNodes = "";
</cfscript>
<!--- does the category already exist --->
<cftry>
<cfset stExisting = objTreemanager.getNode(arguments.categoryid)>
<cfcatch type="any">
<!--- do nothing --->
</cfcatch>
</cftry>
<!--- if the category does not already exist, add it --->
<cfif structIsEmpty(stExisting)>
<!--- TODO: this should be moved to a DAO of some sort --->
<cfquery name="insCategory" datasource="#arguments.dsn#">
INSERT INTO tblCategories(categoryID,title_nav,title_full)
VALUES (
<cfqueryparam value="#arguments.categoryid#" cfsqltype="cf_sql_idstamp">,
<cfqueryparam value="#arguments.title_nav#" cfsqltype="cf_sql_varchar">,
<cfqueryparam value="#arguments.title_full#" cfsqltype="cf_sql_varchar" null="#iif(NOT Len(arguments.title_full), DE(true), DE(false))#">
)
</cfquery>
<cfset objTreemanager.injectIntoHierarchy(arguments.categoryid,arguments.parentid)>
<cfelse>
<cftry>
<cfset strExistingParent = objTreemanager.getParent(arguments.categoryid).id>
<cfcatch type="IllegalStateException.NoParentException">
<cfthrow type="IllegalStateException.NoParentException" message="You cannot edit the root node">
</cfcatch>
</cftry>
<!--- TODO: this should be moved to a DAO of some sort --->
<cfquery name="updCategory" datasource="#arguments.dsn#">
UPDATE tblCategories
SET title_nav=<cfqueryparam value="#arguments.title_nav#" cfsqltype="cf_sql_varchar">,
title_full=<cfqueryparam value="#arguments.title_full#" cfsqltype="cf_sql_varchar" null="#iif(NOT Len(arguments.title_full), DE(true), DE(false))#">
WHERE categoryID=<cfqueryparam value="#arguments.categoryid#" cfsqltype="cf_sql_idstamp">
</cfquery>
<!--- is the parent the same, if not, move it by first removing it from the hierarchy and then reinserting it --->
<cfif strExistingParent NEQ arguments.parentid>
<cfset objTreemanager.removeFromHierarchy(arguments.categoryid)>
<cfset objTreemanager.injectIntoHierarchy(arguments.categoryid,arguments.parentid)>
</cfif>
</cfif>
<cfset tmpNodes = objTreemanager.getNodeList('categoryid,title_nav,lpos')>
<cfset stReturn = queryNew("data,label")>
<cfloop query="tmpNodes">
<cfset queryAddRow(stReturn)>
<cfset querySetCell(stReturn,"label","#repeatString('-',tmpNodes.depth-1)# #tmpNodes.title_nav#")>
<cfset querySetCell(stReturn,"data",tmpNodes.categoryid)>
</cfloop>
<cfreturn stReturn>
</cffunction>
I have zipped the code and it is available at http://www.remotesynthesis.com/downloads/treemanager.zip.
Getting Help with TreeManager.cfc
There isn't alot of documentation with TreeManager.cfc, but for the most part, if you understand nested set trees, it is relatively self-explanatory. There are the two blog posts at Barney's Blog that I linked to within this article (TreeManager CFC Released, How Nested Sets Work. The component itself includes a html version of the cfc method summary and the cfc itself is well commented with thorough explanations of each method. In addition, the download includes an install script for MySQL 4.1 (note: I did not update this for MS SQL). Though this isn't an extensive amount of documentation, I believe, given the limited scope of the component, it is sufficiently complete.
As for contributing, there doesn't appear to be a formalized means of contributing, but I assume you can always contact Barney Boisvert via his blog.
Comments
Posted By Barney / Posted on 11/04/2005 at 4:07 PM
Posted By Daniel / Posted on 01/06/2006 at 11:31 AM
Posted By Brian Rinaldi / Posted on 01/09/2006 at 8:49 PM
Posted By Matthew / Posted on 01/19/2006 at 1:27 PM
Posted By Brian Rinaldi / Posted on 01/19/2006 at 2:11 PM
-Ryan
Posted By Ryan Rinaldi / Posted on 02/03/2006 at 5:45 PM
Posted By Brian Rinaldi / Posted on 02/05/2006 at 6:43 PM
Posted By RituRaj Pandey / Posted on 07/13/2010 at 1:14 AM