Lisitng 1: categories.cfc

<cfcomponent output="false">
<cffunction name="new" access="public" output="false" returntype="query">
<cfscript>
var categoryID = createUUID();
var emptyQuery = QueryNew("categoryID,category,lPos,rPos");

QueryAddRow(emptyQuery);
QuerySetCell(emptyQuery,"categoryID",categoryID);
return emptyQuery;
</cfscript>
</cffunction>

<cffunction name="save" access="public" output="false" returntype="query">
<cfargument name="categoryID" type="uuid" required="true">

<cfscript>
var getCategory = "";
var checkID = get(categoryID=ARGUMENTS.categoryID);

if (checkID.recordCount GT 0) {
getCategory = edit(argumentCollection=ARGUMENTS);
}
else {
getCategory = add(argumentCollection=ARGUMENTS);
}
</cfscript>

<cfreturn getCategory>
</cffunction>

<cffunction name="add" access="private" output="false" returntype="query">
<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<cfargument name="categoryID" type="uuid" required="yes">
<cfargument name="category" type="string" required="yes">
<cfargument name="parentCategoryID" type="uuid" required="no">

<cfscript>
var pos = structNew();
var getCategory = "";

if (isDefined("ARGUMENTS.parentCategoryID")) {
pos=createChildNode(parentCategoryID=ARGUMENTS.parentCategoryID);
}
else {
pos=createRootNode();
}
</cfscript>

<cftransaction>
<cfquery name="insertCategory" datasource="CFDJ">
INSERT INTO categories(categoryID
,category
,lPos
,rPos)
VALUES ('#ARGUMENTS.CategoryID#'
,'#ARGUMENTS.Category#'
,#pos.lPos#
,#pos.rPos#)
</cfquery>
</cftransaction>
<cfscript>
getCategory = get(categoryID=ARGUMENTS.categoryID);
</cfscript>
<cfreturn getCategory>
</cffunction>

<cffunction name="edit" access="private" output="false" returntype="query">
<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<cfargument name="categoryID" type="uuid" required="yes">
<cfargument name="category" type="string" required="yes">

<cfset var getCategory = "">
<cftransaction>
<cfquery name="updateCategory" datasource="CFDJ">
UPDATE Categories
SET Category='#ARGUMENTS.Category#'
WHERE CategoryID = '#ARGUMENTS.CategoryID#'
</cfquery>
</cftransaction>

<cfscript>
getCategory = get(categoryID=ARGUMENTS.categoryID);
</cfscript>

<cfreturn getCategory>
</cffunction>

<cffunction name="delete" access="public" output="false" returntype="query">
<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<cfargument name="CategoryID" type="uuid" required="true">

<!--- get this category's l and r positions --->
<cfscript>
var getCategory = "";
var parent = get(categoryID=ARGUMENTS.categoryID);
</cfscript>

<!--- delete the original node --->
<cftransaction>
<cfquery name="DeleteCategory" datasource="CFDJ">
DELETE FROM Categories
WHERE CategoryID='#ARGUMENTS.CategoryID#'
</cfquery>
<!--- if the category has children, connect children to parent of the original
node (see SQL for Smarties pg 458. Single query seperated to comply with MS Jet
DB Engine accepted syntax) --->
<cfquery name="CloseGap1" datasource="CFDJ">
UPDATE Categories
SET lpos = lPos - 1
WHERE lPos BETWEEN #parent.lPos# AND #parent.rPos#
</cfquery>
<cfquery name="CloseGap2" datasource="CFDJ">
UPDATE Categories
SET lpos = lPos - 2
WHERE lPos > #parent.rPos#
</cfquery>
<cfquery name="CloseGap3" datasource="CFDJ">
UPDATE Categories
SET rPos = rPos - 1
WHERE rPos BETWEEN #parent.lPos# AND #parent.rPos#
</cfquery>
<cfquery name="CloseGap4" datasource="CFDJ">
UPDATE Categories
SET rPos = rPos - 2
WHERE rPos > #parent.rPos#
</cfquery>
</cftransaction>

<cfscript>
getCategory = new();
return getCategory;
</cfscript>
</cffunction>

<cffunction name="get" output="false" access="public" returntype="query">
<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<cfargument name="categoryID" type="uuid" required="false">
<cfargument name="children" hint="if true has precedence over parents equal
true" type="boolean" required="false" default="false">
<cfargument name="parents" type="boolean" required="false" default="false">
<cfargument name="immediate" hint="For children - immediate only"
type="boolean" required="false" default="true">

<cfscript>
var toReturn = "";
var getChildren = "";
var getParents = "";
var getCategory = "";
var getPos = "";
</cfscript>

<!--- searching for the children of a given node --->
<cfif isdefined("ARGUMENTS.categoryID") AND ARGUMENTS.children>
<!--- only the immediate children of a given node --->
<cfif ARGUMENTS.Immediate>
<cfquery name="getChildren" datasource="CFDJ">
SELECT child.*
FROM categories AS parent, categories AS child
WHERE parent.categoryID = '#ARGUMENTS.categoryID#'
AND child.lpos BETWEEN parent.lpos AND parent.rpos
AND NOT EXISTS (SELECT *
FROM categories AS P
WHERE P.lpos BETWEEN parent.lpos AND parent.rpos
AND child.lpos BETWEEN P.lpos AND P.rpos
AND P.categoryID NOT IN (child.categoryID)
AND P.categoryID <> '#ARGUMENTS.categoryID#')
</cfquery>
<!--- else all children, grandchildren, etc of a given node --->
<cfelse>
<cfquery name="getPos" datasource="CFDJ" maxrows="1">
SELECT lPos, rPos
FROM categories
WHERE categoryID = '#ARGUMENTS.categoryID#'
</cfquery> 
<cfquery name="getChildren" datasource="CFDJ">
SELECT *
FROM categories
WHERE lPos BETWEEN #getPos.lPos# AND #getPos.rPos#
ORDER BY lPos
</cfquery>
</cfif>
<cfset toReturn = getChildren>
<!--- searching for the parents of a given node --->
<cfelseif isdefined("ARGUMENTS.categoryID") AND ARGUMENTS.parents>
<cfquery name="getParents" datasource="CFDJ">
SELECT P2.*
FROM Categories AS P1, Categories AS P2
WHERE P1.lPos BETWEEN P2.lPos AND P2.rPos
AND P1.CategoryID = '#ARGUMENTS.CategoryID#'
ORDER BY P2.lPos
</cfquery>
<cfset toReturn = getParents>
<!--- searching only for the details of a given node or a list of all nodes
--->
<cfelse>
<cfquery name="getCategory" datasource="CFDJ">
SELECT *
FROM Categories
<cfif isdefined("ARGUMENTS.CategoryID")>
WHERE CategoryID = '#ARGUMENTS.CategoryID#'
</cfif>
ORDER BY lpos
</cfquery>
<cfset toReturn = getCategory>
</cfif>
<cfreturn toReturn>
</cffunction>

<cffunction name="getAllNodes" access="public" output="false" returntype="query">
<cfargument name="lvl" type="numeric" required="no">

<cfscript>
var getNodes = "";
var getLvlNodes = "";
</cfscript>

<!--- based upon nodeFunctions.cfm as listed in "Dynamic Publishing with
ColdFusion MX" pp 253-255 --->
<cfquery name="getNodes" datasource="CFDJ">
SELECT child.category, COUNT(child.lpos) AS lvl, child.lpos, child.rpos,
child.categoryID
FROM Categories AS parent, Categories AS child
WHERE child.lpos BETWEEN parent.lpos AND parent.rpos
GROUP BY child.lpos, child.rpos, child.category, child.categoryID
ORDER BY child.lpos
</cfquery>

<!--- limit the query to only nodes of a given level within the tree --->
<cfif isDefined("ARGUMENTS.lvl")>
<cfquery name="getLvlNodes" dbtype="query">
SELECT *
FROM getNodes
WHERE lvl = #ARGUMENTS.lvl#
</cfquery>
<cfreturn getLvlNodes>
<cfelse>
<cfreturn getNodes>
</cfif>
</cffunction>

<cffunction name="createRootNode" access="private" output="false"
returntype="struct">
<cfscript>
var Pos = StructNew();
var GetMaxrPos = "";
</cfscript>

<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<!--- find max rPos --->
<cfquery name="GetMaxrPos" datasource="CFDJ">
SELECT MAX(rPos) AS MaxrPos
FROM Categories
</cfquery>

<cfscript>
// if no nodes exist, use 0 for maxrpos
if (NOT isnumeric(GetMaxrPos.MaxrPos)) {
GetMaxrPos.MaxrPos = 0;
}
// create new node with an lpos one above max rpos and an rpos two above max
rpos
Pos.lPos = GetMaxrPos.MaxrPos + 1;
Pos.rPos = GetMaxrPos.MaxrPos + 2;
</cfscript>

<cfreturn Pos>
</cffunction>

<cffunction name="createChildNode" access="private" output="false"
returntype="struct">
<!--- based upon node.cfc as listed in "Dynamic Publishing with ColdFusion MX"
pp 243-248 --->
<cfargument name="parentCategoryID" type="uuid" required="true">

<cfscript>
var Pos = StructNew();
// get parent's l and r positions
var parent = get(CategoryID=ARGUMENTS.parentCategoryID);
// set lpos and rpos to top end of parent's range
Pos.lPos = parent.rPos;
Pos.rPos = parent.rPos + 1;
</cfscript>

<!--- increment the parent's r position and every l and r position above it by
2 --->
<cftransaction>
<cfquery name="updateTree" datasource="CFDJ">
UPDATE Categories
SET rPos = rPos + 2
WHERE rPos >= #parent.rPos#
</cfquery>
<cfquery name="updateTree" datasource="CFDJ">
UPDATE Categories
SET lPos = lPos + 2
WHERE lPos >= #parent.rPos#
</cfquery>
</cftransaction>

<cfreturn Pos> 
</cffunction>
</cfcomponent>

Listing 2: addCategories.cfm

<cfscript>
// move defining the request variables to your application.cfm file
REQUEST.dsn = "CFDJ";
REQUEST.mapping = "criticaldigital.cfdj_article_code";

function RemoveEmptyStructureKeys(Structure) {
var newStructure = StructNew();
for (i=1;i LTE listLen(structKeyList(ARGUMENTS.Structure));i=i+1) {
NAME = listGetAt(structKeyList(ARGUMENTS.Structure),i);
if (NOT isSimpleValue(ARGUMENTS.Structure[NAME])) {
"newStructure.#NAME#" = ARGUMENTS.Structure[NAME];
}
else if (ARGUMENTS.Structure[NAME] NEQ "") {
"newStructure.#NAME#" = ARGUMENTS.Structure[NAME];
}
}
return newStructure;
}

// create the categories object
objCategories = CreateObject('component', '#REQUEST.Mapping#.categories');

if (isdefined("FORM.fieldNames")) {
if (FORM.Category EQ "") {
error="Your category must have a name";
}
else {
// remove any empty values from the FORM structure
categoryToSave = removeEmptyStructureKeys(FORM);
// save the category
saveCategory = objCategories.save(argumentCollection=categoryToSave);
}
}

// get the category information
if (isDefined("URL.categoryID")) {
getCategory = objCategories.get(CategoryID=URL.CategoryID);
getParent = objCategories.get(Parents=true,CategoryID=URL.CategoryID);
parentID = getParent.categoryID[evaluate(getParent.RecordCount - 1)];
}
// get an empty category query
else {
getCategory = objCategories.new();
}

// get all nodes to prefill the parent category select box
getCategories = objCategories.getAllNodes();
</cfscript>

<cfoutput>
<table>
<cfif isdefined("error")>
<tr>
<td colspan="2">#error#</td>
</tr>
</cfif>
<form action="#CGI.SCRIPT_NAME#" method="post">
<input name="categoryID" type="hidden" value="#getCategory.categoryID#">
<tr>
<td>Category Name</td>
<td><input name="category" type="text" value="#getCategory.category#" size="40"
maxlength="255"></td>
</tr>
<tr>
<td>Parent Category (optional)</td>
<td><SELECT NAME="parentCategoryID" <cfif
isdefined("parentID")>disabled="true"</cfif>>
<OPTION VALUE="">Choose One</OPTION>
<cfloop query="getCategories">
<OPTION VALUE="#getCategories.CategoryID#" <cfif isdefined("parentID")
AND getCategories.categoryID EQ
parentID>SELECTED</cfif>>#repeatString("-", getCategories.lvl -
1)##getCategories.category#</OPTION>
</cfloop>
</SELECT></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input name="submitted" type="submit" value="Add/Edit Category"></td>
</tr>
</form>
</table>
</cfoutput>

Listing 3: index.cfm

<cfscript>
// move defining the request variables to your application.cfm file
REQUEST.mapping = "criticaldigital.cfdj_article_code";

objCategories = CreateObject('component', '#REQUEST.Mapping#.categories');

// get all root level categories to propogate your menu
getCategories = objCategories.getAllNodes(lvl=1);
if (NOT isDefined("URL.categoryID")) {
URL.categoryID = getCategories.categoryID;
}

// get the immediate children of the specified category to propogate a submenu
qryChildren = objCategories.get(children=true, immediate=true,
categoryID=URL.categoryID);

// get the parents of a given category to create breadcrumbs
qryParents = objCategories.get(parents=true, categoryID=URL.categoryID);
</cfscript>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Nested Set Model Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>

<cfoutput>
<p><strong>Root-level categories</strong></p>
<p>
<cfloop query="getCategories">
<a href="#CGI.SCRIPT_NAME#?categoryID=#getCategories.categoryID#">
#getCategories.category#</a>
<cfif getCategories.recordCount NEQ getCategories.currentRow>
&nbsp;|&nbsp;
</cfif>
</cfloop>
</p>

<p><strong>Immediate Subcategories</strong></p>
<p>
<!--- start at row 2 because the first item returned is always the category you are
in --->
<cfloop query="qryChildren" startrow="2">
<a href="#CGI.SCRIPT_NAME#?categoryID=#qryChildren.categoryID#">
#qryChildren.category#</a>
<cfif qryChildren.recordCount NEQ qryChildren.currentRow>
&nbsp;|&nbsp;
</cfif>
</cfloop>
</p>

<p><strong>Breadcrumbs</strong></p>
<p>
<cfloop query="qryParents">
<a href="#CGI.SCRIPT_NAME#?categoryID=#qryParents.categoryID#">#qryParents.category#
<cfif qryParents.currentRow NEQ qryParents.recordCount>
>
</cfif>
</a>
</cfloop>
</p>
</cfoutput>
</body>
</html>