At my new job, we use Ext fairly regularly to get some pretty cool AJAXiness up in the mix of things.  To familiarize myself with Ext (I’ve not really used it until now), I recently downloaded the newest version and have been playing around with it quite a bit.

One of the elements I like quite a bit is the data grid.  Like most other things, Ext makes it pretty simple to pull off a slick sortable, pageable data grid with very little work.

Of course, given that I use ColdFusion alot, I decided to hook up a grid to a remote CFC call.  While the call itself is easy enough to do, there is one issue: the JSON that CF returns is in a format that Ext cannot quite understand.

Now there are a bunch of pre-built converters out there, some more robust than others (and a few I couldn’t quite get to work).

So in the spirit of trying to figure it out for myself, I wrote up a simple function in ColdFusion that will take a regular query and convert it into happy JSON that Ext can deal with.

First, here’s our function to create our generic query:

<cffunction name="getPosts" access="remote" returnformat="json" output="false" hint="Gets list/detail of posts">
     <cfargument name="limit" type="numeric" required="no">
     <cfargument name="start" type="numeric" required="no">
     <cfargument name="sort" required="no" type="string">
     <cfset var rsPosts = "">
     <cfquery name="rsPosts" datasource="thegathering">
           SELECT postID,title,date,content
           FROM posts
           ORDER BY #arguments.sort#
     </cfquery>
     <cfset clist = 'postID,title,date'>
     <cfset arrRecords = convertQueryToExtJSON(rsPosts,clist,arguments.limit,arguments.start)>
     <cfset s = {rows=arrRecords,dataset=#rsPosts.RecordCount#}>
     <cfreturn s />
</cffunction>

So the first 10 lines or so are pretty starightforward.  After getting the query, I populate a quick list of columns that I’d like to get turned into JSON goodness.  Next, I pass along some arguments to another function–convertQueryToExtJSON()–where the JSON transformation will happen.

Basically, to get the JSON transformation right, we need to create an array of structures in ColdFusion.  Once we have this, we can pass it back to our calling function and let the returnFormat=JSON basically do the rest.  The trick, of course, is making this array of structures.

As with most things, however, CF makes this super easy.  Here’s my second function:

<cffunction name="ConvertQueryToExtJSON" returntype="any" access="public">
      <cfargument name="query" type="query" required="yes" />
      <cfargument name="clist" type="string" required="yes" />
      <cfargument name="limit" type="numeric" required="yes" />
      <cfargument name="start" type="numeric" required="yes" />
      <cfscript>
           arrRecords = arrayNew(1);
           if(arguments.start==0) {
                counter = 1;
           }
           else {
                counter = arguments.start;
           }
           for(i=1;i<=arguments.limit;i++)  {
                strResults = structNew();
                for(x=1;x<=listLen(clist);x++) {
                     strResults[ucase(listGetAt(clist,x))] = query[listGetAt(clist,x)][counter];
                }
                arrRecords[i] = strResults;
                counter = counter+1;
          }
          return arrRecords;
     </cfscript>
</cffunction>

Again, pretty straightforward.  First, we create an empty, one-dimension array that will hold our structures.  Next, we start a loop that will progress for as many steps as are equal to the argument “limit” (e.g., our “page” size).  At each iteration, we create a new structure that will hold our query row results. Also, we will begin a loop over the column list that we passed in, setting a new key for every column that we want to return in our results.  Using bracket notation, we can easily match the column list item to the corresponding column in our query.

Finally, to close out each major iteration, we simply add our filled-struct to our master array; once all the looping is done, we can return the result.  That’s it!

Now as I mentioned, I am certainly not the first person to do this, and I’m sure there are a lot grander solutions to this than what I’ve offered here (like using cfgrid!!!).  However, this feels pretty simple and elegant to me, so I’m happy with it for the time being.

EDIT: My apologies.  I was going to post a demo, but then realized that GoDaddy is dumb and only offers CFMX7…stupid GoDaddy…