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.
I am in the process of determining myself the best possible solution to the same problem. I found a component called "DataTypeConvert" months ago which has many utility methods for conversion of data from one type to another. In particular the QueryToXML function I beleive has simular functionality to the UDF's posted. I have yet to try it myself with the Flex Tree but have used it for other purposes. I wanted to share it so you and others could evaluate its worth. Who knows, it may save some time in the long run.
The component can be downloaded from the House Of Fusion - http://www.houseoffusion.com/groups/CF-Talk/thread...:42830. Maker sure its DataTypeConvert v1.1.4 CFC Update.
I then use a recusive xsl transform to generate a nested, ordered xml document. I prefer xhtml with nested unordered lists.
These functions are quite helpful. But I do seem to be having an issue with error nodes.
I created a test query that has two records, each with company, department, name, phone, and email as fields (text strings). I set the columns to "company,department,name" as you mentioned. But when I set the dataprovider of the mx:Tree to the result, I get
+ root
---[type Function],[type Function],[type Function],[type Function],[type Function],[type Function],[type Function]
---+ Company
-------+ Department 1
-------------+ Name 1
-------+ Department 2
-------------+ Name 2
---[type Function],[type Function],[type Function],[type Function],[type Function],[type Function],[type Function]
What are the [type Function] nodes about? And how does one get rid of them?
--- Ben
I was actually able to get rid of the [type Function],[type Function],[type Function],[type Function],[type Function],[type Function],[type Function] by removing all CRLF's in the return XML. So... rewriting BuildTreeXML so that the data is built thusly:
<cfxml variable="xmlTree">
<cfoutput><node label="#ARGUMENTS.root#">#BuildTreeXMLProcess(data, cols)#</node></cfoutput>
</cfxml>
fixed the problem. But the question is... why.
The one part of the equation that I didn't mention was that I'm using Cairngorm for the app (in addition to using Flex 1.5, rather than 2.0). And I'm seeing a lot of oddness with what Cairngorm does with complex objects. The reason I started looking at your solution in the first place was because I didn't seem to be able to get a structure to work to populate a Tree control. Any time one of the child elements has a data struct, I can get at the values with getData() as expected, but the tree also insists on building an extra node with [object Object] in the label.
So I'm not sure what's going on.
works a treat, except for nodes that are in the root:
root
--root node
--root node
--non-root node
-----node
-----node
-----node
any updates? any ideas?
thanks.
<cffunction name="BuildTreeXML" returntype="xml" access="public" output="no">
<cfargument name="nongroupedData" type="query" required="yes">
<cfargument name="nongroupedCols" type="string" required="yes">
<cfargument name="groupedData" type="query" required="yes">
<cfargument name="groupedCols" 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(nongroupedData,nongroupedCols)#
#BuildTreeXMLProcess(groupedData,groupedCols)#
</node>
</cfoutput>
</cfxml>
<!--- And return it --->
<cfreturn xmlTree />
</cffunction>
I have to build a tree from data returned from a CFQUERY and I feel this is the right solution but, I am brand new to Flex and just barely get a component hooked up and data flowing. I have never had to use XML. I understand the overall concept but am missing a piece that is obvious to the rest of the group. Where do I process this query??? Do I feed this XML builder from the component that originally retrieved the data then return the XML in place of the query back to the calling mxml app or return the data to the mxml directly then feed to the XML builder code. I need to know how the basic plumbing needs to be set up.
Thanks,
Gerald
Would it be possible to tweak the above code in Ben's article here to generate the following kind of structure and spit out the XML file on the fly? It's exactly how I populate my galleries tree, but I have no idea how generate it from a db (make the db, yes, script CF to build the XML file, no):
<?xml version="1.0" encoding="iso-8859-1"?>
<rootdir>
<root label="/">
<item isBranch="true" label="Animals" gURL="model/animals.xml">
<item isBranch="true" label="people" gURL="">
<item isBranch="false" label="Catherine" gURL="model/Catherine.xml"/>
</item>
</item>
<item isBranch="true" label="Worst Case Scenario - KINGDOM" gURL="etc">
<item isBranch="true" label="Worst Case Scenario - ANIMALs" gURL="etc">
<item isBranch="true" label="Worst Case Scenario - MAMMALS" gURL="etc">
<item isBranch="true" label="Worst Case Scenario - PRIMATES" gURL="etc">
<item isBranch="false" label="Worst Case Scenario - MAN" gURL="etc"/>
</item>
</item>
</item>
</item>
</root>
</rootdir>
I picked up SQL for Smarties...and got very, very scared...:S
Shawn
Thanks in adv!!
James
I had a table call "Category" that stores categoryID, parentID and name. The table can be considered as a list of folders and subfolders.
I needed to retreive data from this table using CFX7 and present their hierarchical relationship under xml structure so that I could feed the xml to <mx:Tree>. What I did was getting the top level categories (where parentID = 0) from the table and running a recursive funtion to search for any subcategory that belongs to the top category. The mechanism was similar to what Ben did, a bit simpler and has worked fine.
Note: my xmlTreeCreator() function return a string instead of xml due to my specific scenario's requirement.
<!--- get top level category --->
<cffunction name="getTopCategory" output="Yes" returntype="query" hint="get detail of all top categories">
<cfargument name="categoryID" type="string" required="Yes"> <!--- to get all category, set categoryID = "all" --->
<cfquery name="qTopCats" datasource="#application.datasource#">
SELECT *,
( SELECT COUNT(*)
FROM category as cat2
WHERE cat2.parentID = category.categoryID
) AS subcat
FROM category
WHERE parentID = 0
<cfif categoryID neq "all">
AND categoryID = <cfqueryparam value="#val(arguments.categoryID)#" cfsqltype="CF_SQL_INTEGER">
</cfif>
</cfquery>
<cfreturn qTopCats>
</cffunction>
<!--- get first-level sub-categories of a category --->
<cffunction name="getSubCategory" output="Yes" returntype="query" hint="get detail of first level sub categories of a category">
<cfargument name="parentID" type="numeric" required="Yes">
<cfset var qSubCats="">
<cfquery name="qSubCats" datasource="#application.datasource#">
SELECT *,
( SELECT COUNT(*)
FROM category as cat2
WHERE cat2.parentID = category.categoryID
) AS subcat
FROM category
WHERE parentID = <cfqueryparam value="#arguments.parentID#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfreturn qSubCats>
</cffunction>
<!--- recursive function to populate categories into tree structure --->
<cffunction name="categoryTreeCreator" returntype="string" hint="populate categories into tree structure">
<cfargument name="datasource" type="query">
<cfset var result="">
<cfset var source="">
<cfset source=arguments.datasource>
<cfloop query="source">
<cfset result=result & "<node label='#source.name#' isBranch='true'>">
<cfif source.subCat gt 0>
<cfinvoke component="#this#" method="getSubCategory" parentID="#source.categoryID#" returnvariable="qSubCat"/>
<cfset result = result & categoryTreeCreator(qSubCat)>
</cfif>
<cfset result = result & "</node>">
</cfloop>
<cfreturn result/>
</cffunction>
<!--- populate categories into xml string --->
<cffunction name="xmlTreeCreator" returntype="string" hint="populate categories into xml string">
<cfinvoke component="#this#" method="getTopCategory" categoryID="all" returnvariable="qTopCat"/>
<cfinvoke component="#this#" method="categoryTreeCreator" datasource="#qTopCat#" returnvariable="xmlString"/>
<cfreturn xmlString/>
</cffunction>
I have the other half, i.e., creating an xml file (with a very structured set of categories, though), but I'm not sure how to combine the two, to populate a Flex 2 tree via HTTPService, so my friend (and anyone else) can update her photo gallery by merely adding the images and names, into categories she wants, changeable at any time.
My only solution was very finite, and it's stopped the project. Do you think I'm far off, with the code below, to change it so it would accept your code above but output an xml file?
Any thoughts or help would be appreciated.
Shawn
Here's what I have, it works, but it's very structured, which won't do the job at hand:
<cfquery datasource="galleryM" name="dbResult">
SELECT galleryname,bgcolor,info,thumb,pic,link
FROM gallery_w_thumbs
ORDER BY galleryname
</cfquery>
<cfsavecontent variable="generatedXML">
<?xml version="1.0" encoding="iso-8859-1"?>
<gallery> <cfoutput query="dbResult" group="galleryname">
<album name="#dbResult.galleryname#"> <cfoutput >
<image color="#dbResult.bgcolor#">
<description>#dbResult.info#</description>
<thumb>#dbResult.thumb#</thumb>
<pic>#dbResult.pic#</pic>
<link>
#dbResult.link#
</link>
</image>
</cfoutput> </album>
</cfoutput> </gallery>
</cfsavecontent>
<cffile action="write" file="#getDirectoryFromPath(getTemplatePath())#\gallery_w_thumbs.xml" output="#trim(generatedXML)#" />
Here is an example of my seft-nested data table that includes categoryID, name and parentID:
categoryID | name | parentID
1 | category1 | 0
2 | category2 | 1
3 | category3 | 1
4 | category4 | 2
Thus you should be able to retreive and populate data into xml without using my code.
In addition, your "gallery_w_thumbs" table has not been normalised. It should be called "picture" table with a picture ID as the picture is unique while the galleryname is referenced serveral times in that table.
By the way, it's just an advise regarding table design to make you program correct and easy to develop. Without changing your database structure, you can still getting your xml generated correctly by a simple change: placing the <cfoutput query="dbResult" group="galleryname"> on top of <gallery> tag and placing the last </cfoutput> under closing </gallery> so all the gallery components will be generated based on how many unique gelleryname you have in your table.
<cfsavecontent variable="generatedXML">
<?xml version="1.0" encoding="iso-8859-1"?>
<cfoutput query="dbResult" group="galleryname">
<gallery>
<album name="#dbResult.galleryname#"> <cfoutput >
<image color="#dbResult.bgcolor#">
<description>#dbResult.info#</description>
<thumb>#dbResult.thumb#</thumb>
<pic>#dbResult.pic#</pic>
<link>
#dbResult.link#
</link>
</image>
</cfoutput></album></gallery>
</cfoutput>
</cfsavecontent>
<cffile action="write" file="#getDirectoryFromPath(getTemplatePath())#\gallery_w_thumbs.xml" output="#trim(generatedXML)#" />
Some links that may help you with ideas on how to integrate to <mx:tree> in Flex:
http://www.adobe.com/devnet/flex/quickstart/workin...
http://examples.adobe.com/flex2/inproduct/sdk/expl... (<-- choose Visual Components, General Controls, Tree)
The only thing about my file inclusion was that it generates an xml file, and to me, that's critical, because the goal is for my photographer friend, and others if the project turns out (I'll give it away) to be able to go into something like a CF-Flex admin page (I actually have the CF-Flex Wizard doing exactly what it needs to), make db changes, and run the script to re-create the 'galleries' xml file, live, such that Flex can populate the navigation tree through HTTPService, always with the fresh data (potential caching aside of course).
The actual tables I gave you in that file above are long-gone. What I need is the ability to create an xml file based on, essentially, your scenario, i.e. something like:
0 | animals | 0
1 | mammals | 0
2 | primates | 1
3 | humans| | 2
4 | my friend george | 3
5 | george in paris in june | 4
6 | george in paris in july | 4
7 | fish | 0
8 | minerals| 8
9 | landscapes | 9
10 | scotland | 9
I hope that makes sense, I'm no expert on this stuff...I get the sense all but the very last of your functions will do exactly that. I don't actually think this type of structure needs any attribute nodes, as, for example, if I want to add a rating or comment ID, that would be per gallery since it would be in the image itself (in, for example, model/georgeInParisJune.xml, below, the file george1.jpg would have a rating and comment ID), not in the file that reads all the galleries. The only exception would be the attribute required to actually point the tree to the correct gallery, I think, with the Flex event pushing the string into the appropriate containers. For example:
0 | animals | 0 | false
1 | mammals | 0 | false
2 | primates | 1 | "model/OneMonkeyFamily.xml"
3 | humans| | 2 | false
4 | my friend george | 3 | false
5 | george in paris in june | 4 | "model/georgeInParisJune.xml"
6 | george in paris in july | 4 | "model/georgeInParisJuly.xml"
7 | fish | 0 | "model/entireGalleryOfFish.xml"
8 | minerals| 8 | "model/Yes_Chocolate_Is_So_A_Mineral.xml"
9 | landscapes | 9 | false
10 | scotland | 9 | etc. etc. etc...
I can imagine that a script which reads adjacency modeled data and spits out an xml file would be incredibly useful for many purposes...but I also suspect it's far beyond me to be able to do it myself, I barely passed CoBOL 101 in the late 80s....
I SORT OF have a very raw workup of this, for a few days anyways, at the following link. Click the button, it will give you a tree. Only Animals and Catherine work, but I think it makes my bad-wording more useful:
http://74.121.143.36/mechanism/bin/Main.html
If this is something you'd be willing to help me out with, feel free to email me (shawn.gibson@yahoo.com or sgibson@ida.ca). At this point, I don't mind paying for it, as I will never be able to figure it out myself.
Shawn
Thank you.