Blog

Blog posts made on 24-Aug-06
24Aug
2006
Building Flex Tree MXML From ColdFusion Queries

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>).

view plain print about
1<!--- Internal query recursion function --->
2<cffunction name="BuildTreeXMLProcess" returntype="string" access="private" output="false">
3    <cfargument name="data" type="query" required="yes">
4    <cfargument name="cols" type="string" required="yes">
5    <cfargument name="where" type="string" required="no" default="0=0">
6
7    <!--- Local vars --->
8    <cfset var result="">
9    <cfset var distinctData="">
10    <cfset var subData="">
11    <cfset var whereClause="">
12
13    <!--- Check if have any more columns in this branch --->
14    <cfif ListLen(ARGUMENTS.cols)>
15
16        <!--- Get distinct values for this column --->
17        <cfquery dbtype="query" name="distinctData">
18        SELECT DISTINCT #ListFirst(ARGUMENTS.cols)# AS col
19        FROM ARGUMENTS.data
20        WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
21        </cfquery>
22    
23        <!--- Loop through distinct data --->
24        <cfloop query="distinctData">
25            <cfset whereClause = ARGUMENTS.where & " AND " & ListFirst(ARGUMENTS.cols) & " = '" & col & "'">
26            <cfquery dbtype="query" name="subData">
27            SELECT *
28            FROM ARGUMENTS.data
29            WHERE #PreserveSingleQuotes(whereClause)#
30            </cfquery>
31            <!--- Any more columns in this branch? --->
32            <cfif ListLen(ListRest(ARGUMENTS.cols))>
33                <!--- Yes, create a node and recurse --->
34                <cfset result=result & "<node label=""#col#"">">

35                <cfset result=result & BuildTreeXMLProcess(subData, ListRest(ARGUMENTS.cols), whereClause)>
36                <cfset result=result & "</node>">
37            <cfelse>
38                <!--- No, create node and populate with all columns --->
39                <cfset result=result & "<node label=""#col#""">
40                <cfset result=result & BuildTreeXMLProcess(subData, ListRest(ARGUMENTS.cols), whereClause)>
41                <cfset result=result & "/>">
42            </cfif>
43        </cfloop>
44
45    <cfelse>
46
47        <!--- Bottom of this branch --->
48        <cfquery dbtype="query" name="subData">
49        SELECT *
50        FROM ARGUMENTS.data
51        WHERE #PreserveSingleQuotes(ARGUMENTS.where)#
52        </cfquery>
53        <!--- Write all columns as name="value" pairs --->
54        <cfloop list="#subData.ColumnList#" index="column">
55            <cfset result = result & " #LCase(column)#=""#subData[column][1]#"" ">
56        </cfloop>
57
58    </cfif>
59
60    <!--- And return it --->
61    <cfreturn result>
62</cffunction>
63
64
65<!--- Build Flex Tree XML from a query --->
66<cffunction name="BuildTreeXML" returntype="xml" access="public" output="no">
67    <cfargument name="data" type="query" required="yes">
68    <cfargument name="cols" type="string" required="yes">
69    <cfargument name="root" type="string" required="no" default="root">
70
71    <!--- Local vars --->
72    <cfset var xmlTree="">
73
74    <!--- Populate XML object --->
75    <cfxml variable="xmlTree">
76    <cfoutput>
77    <node label="#ARGUMENTS.root#">
78    #BuildTreeXMLProcess(data, cols)#
79    </node>
80    </cfoutput>
81    </cfxml>
82    
83    <!--- And return it --->
84    <cfreturn xmlTree />
85
86</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.

Read More ›

24Aug
2006
Using Dates In Flex Data Filtering

A couple of months ago I posted an example that I wrote to show how to filter a Flex DataGrid on the fly. This afternoon a user asked me how to do filtering using date ranges, allowing a date start and end to be specified so as to filter only data that falls within the specified range.

So, here is an updated example. In the interests of simplicity a local ArrayCollection is used. I have added a Date object member named hired (if you were retrieving data from a back-end like ColdFusion then you would likely return a date type which would be converted to an ActionScript Date class so as to be able to easily perform date calculations).

The UI has two DateField controls, startDate and endDate, and a change to either forces a refresh and the filterFunction is applied. The ArrayCollection filterFunction simply checks that the hired date falls between startDate and endDate (allowing null in case no date is selected).

view plain print about
1<?xml version="1.0" encoding="utf-8"?>
2<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
3                layout="vertical"
4                creationComplete="initApp()"
>

5
6    <mx:Script>
7        <![CDATA[
8            // On startup
9

10            public function initApp():void
11            {
12                // Set filter function
13

14                // Be careful to set filterFunction
15

16                // only after ArrayCollection has been
17

18                // populated.
19

20                myData.filterFunction=processFilter;
21            }
22        
23            // Filter function
24

25            public function processFilter(item:Object):Boolean
26            {
27                var result:Boolean=false;
28
29                // If start date is null or <= hire date
30

31                // and end date is null or >= hire date
32

33                if ((dateStart.selectedDate == null
34                        || dateStart.selectedDate <= item.hired)
35                    && (dateEnd.selectedDate == null
36                        || dateEnd.selectedDate >= item.hired))
37                {
38                    result = true;
39                }
40                
41                return result;
42            }
43        ]]>
44    </mx:Script>
45
46    <!-- Data (use ArrayCollection) -->
47    <mx:ArrayCollection id="myData">
48        <mx:source>
49            <mx:Object name="Ben Forta"
50                        location="Oak Park, MI"
51                        phone="(248)555-5555"
52                        hired="{new Date(2002, 8, 17)}" /
>

53            <mx:Object name="Jane Doe"
54                        location="New York, NY"
55                        phone="(212)555-1234"
56                        hired="{new Date(1999, 12, 16)}" /
>

57            <mx:Object name="Jim Jones"
58                        location="Atlanta, GA"
59                        phone="(414)555-1212"
60                        hired="{new Date(2003, 4, 21)}" /
>

61            <mx:Object name="Roberta Roberts"
62                        location="Chicago, IL"
63                        phone="(312)555-4321"
64                        hired="{new Date(2002, 3, 8)}" /
>

65            <mx:Object name="Steve Stevens"
66                        location="Boston, MA"
67                        phone="(617)555-5656"
68                        hired="{new Date(2006, 6, 14)}" /
>

69        </mx:source>
70    </mx:ArrayCollection>
71
72    <!-- UI -->
73    <mx:ApplicationControlBar width="100%">
74        <mx:Label text="Show from"/>
75        <mx:DateField id="dateStart" change="myData.refresh()" />
76        <mx:Label text="to"/>
77        <mx:DateField id="dateEnd" change="myData.refresh()" />
78    </mx:ApplicationControlBar>
79
80    <mx:DataGrid dataProvider="{myData}"
81                    width="100%" height="100%"
>

82        <mx:columns>
83            <mx:DataGridColumn headerText="Name"
84                                dataField="name"/
>

85            <mx:DataGridColumn headerText="Location"
86                                dataField="location"/
>

87            <mx:DataGridColumn headerText="Phone"
88                                dataField="phone"/
>

89            <mx:DataGridColumn headerText="Hired"
90                                dataField="hired"/
>

91        </mx:columns>
92    </mx:DataGrid>
93    
94</mx:Application>

Read More ›