yet another coldfusion blog

An important part of the Meld FormBuilder application I'm working on is the Meld DataProvider, a utility which in this case provides lists for multi-choice items for dropdowns, radioboxes, etc.  I want these lists to hold typed and validated data, and to be able to sort on the secondary columns that can be added beyond the "label", but also want to store all the values in a single database table.  The problem that arises is that we also need to be able to sort results on any of the columns, which because of their generic nature are all type "VARCHAR".

As always, the robust swiss army knife that is ColdFusion comes to the rescue.  The solution is in the ability to CAST datatypes on query columns in a Query of Queries (QoQ) query, regardless of whether it is a manually created query or one drawn from a database.  Take for instance the following dataset:

In this example, all of the database table columns are 'varchar' data types.  In this case I am ordering the query on the label column.  If, however, I do an 'ORDER BY' on the value1 column, I get the following:

This is obviously not the result I desired, as the dates are sorted as strings.  In order to have the dates sort properly, I use the following:

<cfquery name="qSort" dbtype="query"> SELECT label,value1,value2,value3,value4,(CAST(value1 as DATE)) AS SORTCOLUMN FROM qQuery ORDER BY SORTCOLUMN </cfquery>

... which produces the following results:

Now the columns are sorted correctly.  You may also notice that for some reason ColdFusion has also data typed the value4 column as well.  This aberration in the metadata applied to the table isn't really that desirable (what unrequested change in your data set ever is?) but for now I'm focusing upon the desired results, which the CAST function in my QoQ has achieved.

For a more flexible approach, you can use the following QoQ function:

<cffunction name="sortColumnsByDataType" access="public" output="false" returntype="Query"> <cfargument name="arguments.qQuery" type="query" required="true" /> <cfargument name="arguments.sortType" type="string" required="true" /> <cfargument name="arguments.sortColumn" type="string" required="true" /> <cfargument name="arguments.sortDirection" type="string" required="true" /> <cfset var qSort = "" /> <cfquery name="qSort" dbtype="query"> SELECT label,value1,value2,value3,value4 <cfswitch expression="#arguments.sortType#"> <cfcase value="number,numeric"> ,(CAST(#arguments.sortColumn# as INTEGER)) AS SORTCOLUMN </cfcase> <cfcase value="date"> ,(CAST(#arguments.sortColumn# as DATE)) AS SORTCOLUMN </cfcase> <cfcase value="decimal"> ,(CAST(#arguments.sortColumn# as DECIMAL)) AS SORTCOLUMN </cfcase> <cfcase value="time"> ,(CAST(#arguments.sortColumn# as TIME)) AS SORTCOLUMN </cfcase> <cfdefaultcase> ,#arguments.sortColumn# AS SORTCOLUMN </cfdefaultcase> </cfswitch> FROM arguments.qQuery ORDER BY SORTCOLUMN #arguments.sortDirection# </cfquery> <cfreturn qSort /> </cffunction>

This function will return data-typed sorts on your string-columned table.  You could round this out by including all of the supported data types (ColdFusion currently supports BINARY, BIGINIT, BIT, DATE, DECIMAL, DOUBLE, INTEGER, TIME, TIMESTAMP, and VARCHAR data type casting).

One significant caveat of this is that none of your sorts are going to be using original indexes (which you wouldn't want anyway, since they'd be string-sorted indexes).  A second is that you will have to strictly validate the content going into these columns, as even a single non-castable value in your column will cause the whole thing to go pear-shaped.

The main advantage is that you don't have to develop any custom sorting techniques, and that you can easily build upon or modify the results using any of the other wonderful QoQ functions.  It also maintains your data as a ColdFusion query, so you aren't suddenly restricted to manipulating your data via arrays or structures.