ColdFusion queries are flat and non-hierarchical. The Flex <mx:Tree> control supports data specified in a variety of formats, all of which are hierarchical and not flat. Each time I need to populate a Tree control with dynamic query data I find myself jumping through hoops to convert the data into something Flex can use.
The truth is, what I want is something as simple as ColdFusion's <cftree> tag. That tag lets you pass it a query and a list of columns which it uses to build the nested branches. It does not work for every situation (if you have different levels of nesting, for example) but it does work for most tree driven queries.
So, how do you programmatically build nested XML based on ColdFusion queries?
The simplest solution would be to use <cfoutput group="..." in a <cfxml> block. That way you wouldn't have to figure out the nesting levels, <cfoutput>l does that for you. But that will only work for a fixed number of nested groups, you could use dynamic group names but you'd need to hardcode that number of nested groups because there is no way to programmatically add nesting levels on-the-fly. So much for the simple option.
The next option would be to loop through the data manually, creating a new XML object with XMLNew(), and then adding branches with XMLElemNew(). I spent way too much time on this idea only to discover that it's just not practical. ColdFusion's XML manipulation functions make it really easy to add nodes as long as you know where you want to add them, but there is no easy way to add child elements at arbitrary locations (at unknown depths), and there is no way to search an XML object returning a handle that could be used to create a child at the found location. So, no go.
I tried several other ideas too, until I came up with what follows. Now, before I go further I must state that this is a work in progress. The code is not perfect yet, but it does work, it is flexible, and as I have never seen ColdFusion code that uses query-of-queries recursion like this before I just had to post it in its current state.
So, here goes. The following code block contains two UDFs. BuildTreeXMLProcess() is an internal function is should never be called directly. BuildTreeXML() is the main entry point. You pass it a ColdFusion query, a list of columns specifying your nesting order, and an optional top-level identifier (the default root label is "root"). So, if your query had columns company, department, name, phone, and e-mail, and you wanted the tree to list company at the top level, department beneath it, and name beneath that, you'd specify "company,department,name" as the cols value (it behaves just like <cftree>).
<!--- Internal query recursion function --->
<cffunction name="BuildTreeXMLProcess" returntype="string" access="private" output="false">
<cfargument name="data" type="query" required="yes">
<cfargument name="cols" type="string" required="yes">
<cfargument name="where" type="string" required="no" default="0=0">
<!--- Local vars --->
<cfset var result="">
<cfset var distinctData="">
<cfset var subData="">
<cfset var whereClause="">
<!--- Check if have any more columns in this branch --->
<cfif ListLen(ARGUMENTS.cols)>
<!--- Get distinct values for this column --->
<cfquery dbtype="query" name="distinctData">
SELECT DISTINCT #ListFirst(ARGUMENTS.cols)# AS col
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
</cfquery>
<!--- Loop through distinct data --->
<cfloop query="distinctData">
<cfset whereClause = ARGUMENTS.where & " AND " & ListFirst(ARGUMENTS.cols) & " = '" & col & "'">
<cfquery dbtype="query" name="subData">
SELECT *
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(whereClause)#
</cfquery>
<!--- Any more columns in this branch? --->
<cfif ListLen(ListRest(ARGUMENTS.cols))>
<!--- Yes, create a node and recurse --->
<cfset result=result & "<node label=""#col#"">">
<cfset result=result & BuildTreeXMLProcess(subData, ListRest(ARGUMENTS.cols), whereClause)>
<cfset result=result & "</node>">
<cfelse>
<!--- No, create node and populate with all columns --->
<cfset result=result & "<node label=""#col#""">
<cfset result=result & BuildTreeXMLProcess(subData, ListRest(ARGUMENTS.cols), whereClause)>
<cfset result=result & "/>">
</cfif>
</cfloop>
<cfelse>
<!--- Bottom of this branch --->
<cfquery dbtype="query" name="subData">
SELECT *
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
</cfquery>
<!--- Write all columns as name="value" pairs --->
<cfloop list="#subData.ColumnList#" index="column">
<cfset result = result & " #LCase(column)#=""#subData[column][1]#"" ">
</cfloop>
</cfif>
<!--- And return it --->
<cfreturn result>
</cffunction>
<!--- Build Flex Tree XML from a query --->
<cffunction name="BuildTreeXML" returntype="xml" access="public" output="no">
<cfargument name="data" type="query" required="yes">
<cfargument name="cols" type="string" required="yes">
<cfargument name="root" type="string" required="no" default="root">
<!--- Local vars --->
<cfset var xmlTree="">
<!--- Populate XML object --->
<cfxml variable="xmlTree">
<cfoutput>
<node label="#ARGUMENTS.root#">
#BuildTreeXMLProcess(data, cols)#
</node>
</cfoutput>
</cfxml>
<!--- And return it --->
<cfreturn xmlTree />
</cffunction>
Now, I did say that the code is not quite done yet. The biggest limitation for now is that the columns listed in cols must all be string values (no numbers or dates). I'll fix this at some point. For now, if you need to populate Flex Tree controls with ColdFusion query data, this may help.
If you have comments or suggestions, I'd love to hear them.
There are no comments for this entry.
[Add Comment]