Looping Over ColdFusion JSON Queries In jQuery

Posted November 16, 2009 at 9:45 AM

Tags: ColdFusion, Javascript / DHTML

I've talked to people many times about how to loop over ColdFusion queries that have been returned from an AJAX call in JSON format. The serialized format of ColdFusion queries are a bit round-about since they are designed for memory optimization, not necessarily usability. As such, I often wind up telling people that I prefer to convert my queries to arrays of structs before returning them as that format is quite intuitive to work with on the Javascript side.

After discussing this topic with Andy Matthews last week, I wanted to see if could use a jQuery plugin to abstract out the differences between all the (popular) query serialization formats. From what I can reason, there are three main query serialization formats:

SerializeJSON( query )

This method serializes a query using SerializeJSON() with no WDDX compatibility. It returns the query in the following format:

 Launch code in new window » Download code as text file »

  • {
  • "COLUMNS":["ID","NAME"],
  • "DATA":[
  • [1.0,"Kate"],
  • [2.0,"Sarah"],
  • [3.0,"Tricia"],
  • [4.0,"Kit"],
  • [5.0,"Jill"]
  • ]
  • }

As you can see, this breaks the column names and data values into two separate arrays.

SerializeJSON( query, true )

This method serializes a query using SerializeJSON() with the WDDX-compatibility. It returns the query in the following format:

 Launch code in new window » Download code as text file »

  • {
  • "ROWCOUNT":5,
  • "COLUMNS":["ID","NAME"],
  • "DATA":{
  • "id":[1.0,2.0,3.0,4.0,5.0],
  • "name":["Kate","Sarah","Tricia","Kit","Jill"]
  • }
  • }

As you can see, this looks a bit more like a standard ColdFusion query in which we have a RecordCount as well as individual column arrays.

Array of Structs

This method serializes a query using a manual conversion of rows to structs. It returns the query in the following format:

 Launch code in new window » Download code as text file »

  • [
  • {"ID":1.0,"NAME":"Kate"},
  • {"ID":2.0,"NAME":"Sarah"},
  • {"ID":3.0,"NAME":"Tricia"},
  • {"ID":4.0,"NAME":"Kit"},
  • {"ID":5.0,"NAME":"Jill"}
  • ]

As you can see, this is nothing more than an array of structs in which each struct is a collection of name-value pairs in which each name is the name of a column in the row. Here, we lose memory optimization since each column is repeated for each row; but, we have an objectively easier data structure to work with.

I figured the easiest way to abstract out the query iteration would be to create an each-style method in which you pass it the query and a callback function and the callback gets executed in the context of each row. The row context would then be a column-key-indexed struct that would be super user friendly. To see what I am intending, take a look at this:

 Launch code in new window » Download code as text file »

  • eachRow(
  • query,
  • function( rowIndex, row ){
  • console.log( this.ID, row.ID );
  • console.log( this.NAME, row.NAME );
  • }
  • );

Here, the callback takes two arguments - rowIndex (the index of the row being iterated) and row (a struct of name-value pairs in which the name is the column name). While the row is passed as an argument to the callback, the callback is also executed in the context of the row such that you can access the column values using either the row argument or the THIS-based keys.

I took that concept and I built it as a jQuery plugin. To add a better level of organization to this plugin, I created it within a "coldfusion" namespace:

 Launch code in new window » Download code as text file »

  • ;(function( $ ){
  •  
  • // Create a name space for ColdFusion related functionality.
  • jQuery.coldfusion = {};
  •  
  •  
  • // Create a function that will iterate over each row of the
  • // serialized JSON query. This iteration can handle three
  • // types of query structure:
  • //
  • // - Default from SerializeJSON()
  • // - WDDX Compatible from SerializeJSON()
  • // - Array of structs.
  • jQuery.coldfusion.eachRow = function( query, callback ){
  • // Check to see which type of iterator we are going to
  • // use when looping over this query.
  • if ($.isArray( query )){
  •  
  • // This is an array of structs.
  • jQuery.coldfusion.eachRow.arrayIterator(
  • query,
  • callback
  • );
  •  
  • } else if ("ROWCOUNT" in query){
  •  
  • // This is the WDDX-compatible format.
  • jQuery.coldfusion.eachRow.wddxIterator(
  • query,
  • callback
  • );
  •  
  • } else {
  •  
  • // This is the default format.
  • jQuery.coldfusion.eachRow.defaultIterator(
  • query,
  • callback
  • );
  •  
  • }
  •  
  • // Return the jQuery library.
  • return( this );
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // default SerializeJSON() method.
  • jQuery.coldfusion.eachRow.defaultIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the data array.
  • for (var i = 0 ; i < query.DATA.length ; i++){
  •  
  • (function( rowIndex ){
  • var row = {};
  •  
  • // Loop over the column names to create the data
  • // collection as column-value pairs.
  • $.each(
  • query.COLUMNS,
  • function( index, column ){
  • row[ column ] = query.DATA[ rowIndex ][ index ];
  • }
  • );
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( row, rowIndex, row );
  •  
  • })( i );
  •  
  • }
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // SerializeJSON() method that returns WDDX-compatible data.
  • jQuery.coldfusion.eachRow.wddxIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the records.
  • for (var i = 0 ; i < query.ROWCOUNT ; i++){
  •  
  • (function( rowIndex ){
  • var row = {};
  •  
  • // Loop over the column names to create the data
  • // collection as column-value pairs.
  • $.each(
  • query.DATA,
  • function( column, values ){
  • row[ column.toUpperCase() ] = values[ rowIndex ];
  • }
  • );
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( row, rowIndex, row );
  •  
  • })( i );
  •  
  • }
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // query as an array of structs.
  • jQuery.coldfusion.eachRow.arrayIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the records.
  • for (var i = 0 ; i < query.length ; i++){
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( query[ i ], i, query[ i ] );
  •  
  • }
  • };
  •  
  • })( jQuery );

Because Javascript treats functions as first class citizens, I was able to store the three iteration methods as properties of the eachRow() method. To see this in action, let's take a look at an example:

 Launch code in new window » Download code as text file »

  • <!DOCTYPE HTML>
  • <html>
  • <head>
  • <title>Looping Over ColdFusion JSON Queries In jQuery</title>
  • <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
  • <script type="text/javascript" src="jquery.query-loop.js"></script>
  • <script type="text/javascript">
  •  
  • // When the DOM loads, gather the query data and output
  • // it as lists.
  • jQuery(function( $ ){
  •  
  • // Get the results dive for population.
  • var results = $( "#results" );
  •  
  •  
  • // This method will handle writing the query to the
  • // DOM as an ordered list.
  • var render = function( type, query ){
  • var items = [];
  •  
  • // Add the type header.
  • results.append( "<h2>" + type + "</h2>" );
  •  
  • // Loop over the query to add each row to the
  • // output string buffer. The eachRow() method is
  • // executed in the context of the row collection.
  • $.coldfusion.eachRow(
  • query,
  • function( rowIndex ){
  • items.push(
  • "<li>" +
  • this.NAME +
  • " ( ID: " + this.ID + " )" +
  • "</li>"
  • );
  • }
  • );
  •  
  • // Append the rows as an ordered list.
  • results.append(
  • "<ol>" +
  • items.join( "" ) +
  • "</ol>"
  • );
  • };
  •  
  •  
  • // Gather the query using the default method.
  • $.getJSON(
  • "./query.cfm",
  • function( query ){
  • render( "Default Method", query );
  • }
  • );
  •  
  • // Gather the query using the wddx-compatible method.
  • $.getJSON(
  • "./query.cfm?serializeMethod=wddxCompatible",
  • function( query ){
  • render( "WDDX-Compatible Method", query );
  • }
  • );
  •  
  • // Gather the query using the array of structs method.
  • $.getJSON(
  • "./query.cfm?serializeMethod=arrayOfStructs",
  • function( query ){
  • render( "Array-of-Structs Method", query );
  • }
  • );
  •  
  • });
  •  
  • </script>
  • </head>
  • <body>
  •  
  • <h1>
  • Looping Over ColdFusion JSON Queries In jQuery
  • </h1>
  •  
  • <div id="results">
  • <!--- This will be populated dynamically. --->
  • </div>
  •  
  • </body>
  • </html>

Here, we are calling a ColdFusion page to return a query object in the three different serialization formats. Each type of serialized query is then iterated over using the eachRow() plugin and its contents are written to the DOM. Running the above code, we get the following output:

Default Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

WDDX-Compatible Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

Array-of-Structs Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

As you can see, each style of query serialization was iterated over usingg the exact samemethodologyy - the eachRow() jQuery plugin.

If you care to see what the ColdFusion side of things is doing in order to create the various serialization formats, here you go:

 Launch code in new window » Download code as text file »

  • <!---
  • Param the type of return value that we are going to be using
  • when we serialize the query object. There are three different
  • ways to return the query data:
  •  
  • default: serializeJSON( query )
  • wddxCompatible: serializeJSON( query, true )
  • arrayOfStructs: Create an array of structs.
  • --->
  • <cfparam
  • name="url.serializeMethod"
  • type="string"
  • default="default"
  • />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  • <!--- Build the query object. --->
  • <cfset girls = queryNew( "" ) />
  •  
  • <!--- Add the ID column. --->
  • <cfset queryAddColumn(
  • girls,
  • "id",
  • "cf_sql_integer",
  • listToArray( "1,2,3,4,5" )
  • ) />
  •  
  • <!--- Add the name column. --->
  • <cfset queryAddColumn(
  • girls,
  • "name",
  • "cf_sql_varchar",
  • listToArray( "Kate,Sarah,Tricia,Kit,Jill" )
  • ) />
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have created our query object, let's serialize it.
  • But first, we need to see which method of serialization we are
  • going to use.
  • --->
  • <cfswitch expression="#url.serializeMethod#">
  •  
  • <!---
  • arrayOfStructs. This will convert each row of the query
  • to a struct and return the aggregate of row-stucts in an
  • array.
  • --->
  • <cfcase value="arrayOfStructs">
  •  
  • <!---
  • Before we can serialize the query, we need to convert
  • it to an array of structs.
  • --->
  • <cfset rows = [] />
  •  
  • <!--- Loop over the query to convert it. --->
  • <cfloop query="girls">
  •  
  • <!--- Create a row struct. --->
  • <cfset row = {} />
  •  
  • <!--- Add each column to our struct. --->
  • <cfloop
  • index="column"
  • list="#girls.columnList#"
  • delimiters=",">
  •  
  • <cfset row[ column ] = girls[ column ][ girls.currentRow ] />
  •  
  • </cfloop>
  •  
  • <!--- Append the row struct to the row array. --->
  • <cfset arrayAppend( rows, row ) />
  •  
  • </cfloop>
  •  
  • <!---
  • Now that we have converted our query to an
  • array of structs, we can serialize it using the
  • serializeJSON() method.
  • --->
  • <cfset serializedQuery = serializeJSON( rows ) />
  •  
  • </cfcase>
  •  
  • <!---
  • wddxCompatible. This will use the SerializeJSON() method,
  • but it will pass in TRUE as the optional second parameter
  • for a WDDX compatible format.
  • --->
  • <cfcase value="wddxCompatible">
  •  
  • <!--- Seraialize the query. --->
  • <cfset serializedQuery = serializeJSON( girls, true ) />
  •  
  • </cfcase>
  •  
  • <!--- The default is just to use SerailizeJSON(). --->
  • <cfdefaultcase>
  •  
  • <!--- Seraialize the query. --->
  • <cfset serializedQuery = serializeJSON( girls ) />
  •  
  • </cfdefaultcase>
  •  
  • </cfswitch>
  •  
  •  
  • <!---
  • Now that we have our serialized data, let's convert it
  • into binary so we can have a bit more control over the
  • streaming.
  • --->
  • <cfset binaryResponse = toBinary( toBase64( serializedQuery ) ) />
  •  
  •  
  • <!--- Set the header. --->
  • <cfheader
  • name="content-length"
  • value="#arrayLen( binaryResponse )#"
  • />
  •  
  • <!--- Stream the content back as JOSN. --->
  • <cfcontent
  • type="application/x-json"
  • variable="#binaryResponse#"
  • />

Hopefully this will help you abstract away the confusingg ColdFusion query serialization format. Once again, jQuery for the win!

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Other Searches  |  Print Page




Reader Comments

Britta
Nov 16, 2009 at 11:43 AM // reply »
1 Comments
10 Points

Hey,
Thanks for the ColdFusion Tutorial.
It was very helpfull. Ive learnd a lot of them.
This style and your code-template helps me for my other programming ideas.

Thx Britta.


Brian Lang
Nov 16, 2009 at 11:45 AM // reply »
31 Comments
329 Points

Looks useful. Going to try this on my next AJAX project.


Ben Nadel
Nov 16, 2009 at 11:51 AM // reply »
7,572 Comments
36,838 Points

@Britta, @Brian,

Cool.


Sami Hoda
Nov 16, 2009 at 2:15 PM // reply »
3 Comments
24 Points

Very nice Ben. This is extremely useful.


Ben Nadel
Nov 16, 2009 at 2:17 PM // reply »
7,572 Comments
36,838 Points

@Sami,

Thanks my man; glad you like it. I think query serialization is definitely one of the trickiest things that people come up against when crossing the ColdFusion-Javascript chasm.


Mark Morrison
Nov 16, 2009 at 5:34 PM // reply »
4 Comments
41 Points

Ben,

Your tutorials are always so well written. Great info. I learned more than I expected.


Ben Nadel
Nov 16, 2009 at 5:47 PM // reply »
7,572 Comments
36,838 Points

@Mark,

Wooohooo! Thanks, that's awesome.


Michael Mosler
Nov 17, 2009 at 8:53 AM // reply »
1 Comments
10 Points

Thanks! Works like a charm


Ben Nadel
Nov 17, 2009 at 8:54 AM // reply »
7,572 Comments
36,838 Points

@Michael,

Sweet. I particularly like that the callback function is called in the context of the row itself such that the column values can be accessed off of the THIS reference.


Pritesh Patel
Nov 17, 2009 at 1:47 PM // reply »
1 Comments
12 Points

Thanks for such pretty useful code. I really love this.


Hakan
Nov 18, 2009 at 6:41 AM // reply »
1 Comments
12 Points

Thank you for your very useful code


Daniel Lancelot
Mar 17, 2010 at 8:39 PM // reply »
1 Comments
10 Points

Thanks Ben - this is just what I needed - just getting up to speed with jQuery - and your posts are accelerating that process substantially :)


Ben Nadel
Mar 19, 2010 at 8:50 AM // reply »
7,572 Comments
36,838 Points

@Daniel,

Awesome my man. If there is anything you want to see in particular, just let me know and I'll try to write up something worthwhile.


Post Comment  |  Ask Ben

Recent Blog Comments
Gojko
Mar 19, 2010 at 7:26 PM
MySQL 3/4 - com.mysql.jdbc.Driver And allowMultiQueries=true
Thank you very much for this post. Adding allowMultiQueries="true" in context.xml didn't help until I added it to url as allowMultiQueries=true Good idea is to use prepared statements and it will he ... read »
Jim
Mar 19, 2010 at 4:49 PM
Nobody Puts Baby In The Corner!
Wow. This is like suddenly finding a support group for your secret shame. I'm not alone! I always liked this movie, even though it is extremely cheesy. I just wish Jennifer Grey hadn't gotten the ... read »
Bryan Hurley
Mar 19, 2010 at 4:47 PM
Application.cfc OnRequest() Method Affects OnError() Arguments
@Jason and @Ben, I've been doing some CF9 refactoring on our systems and noticed an odd occurrence with onError as well. Found a way to work around my problem, but what I saw was... Background: Our ... read »
Jim
Mar 19, 2010 at 4:44 PM
Shoot 'Em Up Starring Clive Owen And Paul Giamatti
I actually enjoyed this movie quite a lot. It was different, certainly, but I think they were going for more of a Quentin Tarentino-"wow, that was weird"-vibe than an actual spoof. Once I realize ... read »
Billy
Mar 19, 2010 at 4:34 PM
An Intensive Exploration Of jQuery With Ben Nadel (Video Presentation)
Hey I guess the video is down. Is there anyway you can upload to youtube or vimeo or some other service? Greatly appreciated. ... read »
Jose Galdamez
Mar 19, 2010 at 4:24 PM
ColdFusion CFPOP - My First Look
@Ben Thanks for the follow up! The root of the problem had to do with being able to trace bounced emails to specific records in a DB table. Let's say you run an email campaign and you get 1,000 bou ... read »
AmberCF
Mar 19, 2010 at 4:15 PM
SQL COUNT( NULLIF( .. ) ) Is Totally Awesome
Thank you Ben and Tony! Either of these work for the summary report I am working on and the info is much appreciated! I think I like Tony's a little better because I won't have to educate every ... read »
Sean Smith
Mar 19, 2010 at 3:35 PM
ColdFusion Path Usage And Manipulation Overview
@Ben, Sorry. Clarification. expandpath worked for me in application.cfc, but not in other templates. ... read »