ColdFusion queries are flat and non-hierarchical. The Flex
The truth is, what I want is something as simple as ColdFusion’s
So, how do you programmatically build nested XML based on ColdFusion queries?
The simplest solution would be to use
SELECT DISTINCT #ListFirst(ARGUMENTS.cols)# AS col
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
SELECT *
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(whereClause)#
SELECT *
FROM ARGUMENTS.data
WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
#BuildTreeXMLProcess(data, cols)#
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.
Building Flex Tree MXML From ColdFusion Queries
19 responses to “Building Flex Tree MXML From ColdFusion Queries”
-
Ben – I’ve been working on a means by which to represent our Active Directory structure and have experienced the same frustrations as you. I like your suggested solution to the problem and am going to give it a whirl. Thanks for the timely and excellent post!
-
Ben,
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.cfm/threadid:42830. Maker sure its DataTypeConvert v1.1.4 CFC Update. -
Web site navigation is a very similar problem and I have solved it by taking the query and converting it to generic flat xml document such as <resultset><row><id>{id}</id><parentid>{parentid}</parentid><name>{name}</name></row>…</resultset>
I then use a recusive xsl transform to generate a nested, ordered xml document. I prefer xhtml with nested unordered lists. -
Ben,
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? -
Carl, try calling the CFC method with a CFINVOKE in a CF page and dump the results, make sure it is in fact creating valid XML and that it has the data you expect.
— Ben -
Thanks Ben. I’ve been doing that myself, as well as watching the traffic with both the NetConnection Debugger and Kevin Langdon’s ServiceCapture… and the data is as *I* expect (though, that doesn’t necessarily mean it’s correct 😉 )
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. -
ben,
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. -
just to follow up, i modified BuildTreeXML to accept two queries, one of the root nodes data & one of the rest:
<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> -
Ben,
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 -
I’m working a bit backwards here. I got the tree in Flex 2 and the XML, but ultimately, the entire thing in meant to be generated by reading the values from a real SQL/MySQL/Access db. The db part is easy enough, but the script to generate the XML, well, I’ve never gotten past very structured datasets where each ‘node’ is exactly the same, no one-nest, two-nest, three-nest, four…etc. I can build, in other words, a CF script to generate the galleries referenced in the gURL attributes below, because they are very uniformly structured.
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 -
Is there a way to plug an XML file directly? suppose i have a nav hierarchy in xml and i want to view it as a tree (to let the end user edit it visually).. how would i go about that?
Thanks in adv!!
James -
This only focuses on using CF queries and functions to generate a result set in hierarchical structure to feed in <mx:Tree>
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> -
Jane: you seem to have done what I’ve found impossible (I’m a designer not a coder), in creating the CF needed to do exactly what I need (build tree xml out of an adjacency model source).
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)#" /> -
Shawn, in my understanding, your data table is not seft-nested table struture.
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/working_with_tree/
http://examples.adobe.com/flex2/inproduct/sdk/explorer/explorer.html (<– choose Visual Components, General Controls, Tree) -
Thanks Jane:) I might have done a disservice here by including the file I was trying to convert. The actual structure I’m hoping to be able to use, near as I can tell, is definitely of the type you have created (for example, this allowed me to wrap my head around the concept, in general terms: http://sqllessons.com/categories.html).
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 -
I am new to Flex and my first project is to use the MXML Tree from a CF query. I can’t get the syntax correct to call the cfc correctly. I am trying to do a very simple example using the ArtGallery database to group by name, description, title. Does anyone have an example? Thank you.
-
Ben – I’m new to Flex (since CFUNITED), I’ve been working in ColdFusion for a few years. My first project is to create a dynamic org chart using the tree function in Flex. I can’t get the syntax correct to call the query and pass the arguments correctly. Do you have an example of how the function is called. I really need help, I think I am close, but Flex is proving to be more of a challenge than I thought. I have several books, but none really address ColdFusion and the Tree function. HELP!
Thank you. -
I am trying to create a tree control in flex and I am working with a self referencing table ‘Category’ just like the one Jane mentioned. Did anyone of you succeed in generating hierarchical xml document? Pleas help!
-
Please ignore my previous comment. Thanks to Jane I was able to do this with the help of her code. Here it is
<!— populate categories into xml string —>
<cffunction name="xmlTreeCreator" access="remote" hint="populate categories into xml string" returntype="xml">
<cfset var xml = "">
<cfinvoke component="#this#" method="getTopCategory" categoryID="all" returnvariable="qTopCat"/>
<cfinvoke component="#this#" method="categoryTreeCreator" datasource="#qTopCat#" returnvariable="xmlString"/>
<cfxml variable="xml">
<root>
#xmlstring#
</root>
</cfxml>
<cfreturn xml>
</cffunction>
Leave a Reply