There's been a few recent blogs posts covering how to convert the JSON returned by ColdFusion's serializeJSON function to a more standard pattern in order to use data with various grids or JavaScript templates. Those posts covered client-side conversions, here's how to generate the JSON on the server.
ColdFusion and JSON
Let's use this function, with a simple query:
2 <cfargument name="term" type="string" required="true" />
3 <cfset var rs = {} />
4 <cfquery name="rs.q" datasource="cfbookclub">
5 SELECT DISTINCT
6 bookid,
7 title,
8 genre
9 FROM
10 books
11 WHERE
12 title LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar" />
13 ORDER BY
14 genre, title
15 </cfquery>
16 <cfreturn serializeJSON( rs.q ) />
17</cffunction>
This is using the (case sensitive) Embedded Apache Derby database "cfbookclub" that comes with the base install of ColdFusion. If I make this call:
My JSON will look like this:
2 "COLUMNS":["BOOKID","TITLE","GENRE"],
3 "DATA":[
4 [8,"Apparition Man","Fiction"],
5 [2,"Shopping Mart Mania","Non-fiction"]
6 ]
7}
If I change the function's return to <cfreturn serializeJSON( rs.q, true ) />
2 "ROWCOUNT":2,
3 "COLUMNS":["BOOKID","TITLE","GENRE"],
4 "DATA":{
5 "BOOKID":[8,2],
6 "TITLE":["Apparition Man","Shopping Mart Mania"],
7 "GENRE":["Fiction","Non-fiction"]
8 }
9}
But what we really want is an array of structs. Each struct is an object representation of a row in the query. This is what I've known from Flex development as an ArrayCollection.
2 "data":[
3 {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4 {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5 ]
6}
Client-side Conversion
Here's a few blog posts that cover how to convert ColdFusion's standard JSON to this more standard JSON format:
- Steve Cutter created the jQuery plugin serializeCFJSON
- Ray Camden shows the function cfQueryNormalize to do the same without needing jQuery.
But we want to serve up our data is this JSON format directly from the server.
Server-side Conversion
The day after Ray put up his first Handlebars demo, I got to work using Handlebars in an internal application I'm building. Handlebars is a drop-dead simple JavaScript template engine which I'll talk more about it later.
A few days ago, Julian Halliwell put up a post on this same approach, but his returns both the original CF JSON and the converted JSON.
The ArrayCollection.cfc only returns the converted JSON and allows you to convert any query on the fly.
ArrayCollection.cfc
Here's the CFC, which should always be created as a singleton. Props to Ben Nadel for giving me a place to start.
2
3 <cffunction name="init" access="public" output="false" returntype="ArrayCollection">
4 <cfset setContentType("json") />
5 <cfset setDataHandle(true) />
6 <cfset setDataHandleName("data") />
7 <cfreturn this />
8 </cffunction>
9
10 <cffunction name="$renderdata" access="public" output="false" returntype="string" hint="convert a query to an array of structs">
11 <cfset var rs = {} />
12 <cfset var rs.q = variables.data />
13 <cfset rs.results = [] />
14 <cfset rs.columnList = lcase(listSort(rs.q.columnlist, "text" )) />
15 <cfloop query="rs.q">
16 <cfset rs.temp = {} />
17 <cfloop list="#rs.columnList#" index="rs.col">
18 <cfset rs.temp[rs.col] = rs.q[rs.col][rs.q.currentrow] />
19 </cfloop>
20 <cfset arrayAppend( rs.results, rs.temp ) />
21 </cfloop>
22 <cfset rs.data = {} />
23 <cfif hasDataHandle()>
24 <cfset rs.data[getDataHandleName()] = rs.results />
25 <cfelse>
26 <cfset rs.data = rs.results />
27 </cfif>
28 <cfreturn serializeJSON(rs.data) />
29 </cffunction>
30
31 <cffunction name="setData" access="public" output="false" returntype="void">
32 <cfargument name="data" type="query" required="true">
33 <cfset variables.data = arguments.data />
34 </cffunction>
35
36 <cffunction name="setContentType" access="private" output="false" returntype="void">
37 <cfargument name="contenttype" type="string" required="true" />
38 <cfset variables.contentType = arguments.contentType />
39 </cffunction>
40 <cffunction name="getContentType" access="public" output="false" returntype="string">
41 <cfreturn variables.contentType />
42 </cffunction>
43
44 <cffunction name="setDataHandle" access="public" output="false" returntype="void">
45 <cfargument name="datahandle" type="boolean" required="true" />
46 <cfset variables.dataHandle = arguments.datahandle />
47
48 </cffunction>
49 <cffunction name="hasDataHandle" access="public" output="false" returntype="boolean">
50 <cfreturn variables.dataHandle />
51 </cffunction>
52
53 <cffunction name="setDataHandleName" access="public" output="false" returntype="void">
54 <cfargument name="dataHandleName" type="string" required="true" />
55 <cfset variables.dataHandleName = arguments.dataHandleName />
56 </cffunction>
57 <cffunction name="getDataHandleName" access="public" output="false" returntype="string">
58 <cfreturn variables.dataHandleName />
59 </cffunction>
60
61</cfcomponent>
Converting a Query
Let's go back to my original function and change it to return an ArrayCollection.
2<cfset rs.ac.setData( rs.q ) />
3<cfreturn rs.ac.$renderdata() />
You've already seen the results:
2 "data":[
3 {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4 {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5 ]
6}
So let's change the data handle:
2<cfset rs.ac.setData( rs.q ) />
3<cfset rs.ac.setDataHandleName( "foo" ) />
4<cfreturn rs.ac.$renderdata() />
which returns
2 "foo":[
3 {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4 {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5 ]
6}
And if we just want the array without a data handle:
2<cfset rs.ac.setData( rs.q ) />
3<cfset rs.ac.setDataHandle(false) />
4<cfreturn rs.ac.$renderdata() />
will give us just the array
2 {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
3 {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
4]
Teaser
I'll post this CFC to github later on, but soon I'll post an example of using this with Handlebars AND I'll tell you why I chose the name $renderdata() instead of renderdata().
