Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with:

Ask Ben: Converting a Query to an Array

By Ben Nadel on

A new ColdFusion programmer emailed me recently asking:

How can I convert a ColdFusion query into an array?

When it comes to this type of conversion, there are a few things that you have to take into account. For starters, is it worth it? The ColdFusion query object is a very powerful, very flexible array-like object to begin with. It can already be accessed directly without having to loop over the record set. Is this conversion something you really need to do? Second, if you do want to make the conversion, what structure do you want? You can either mimic the existing query notation by creating a "Structure of Arrays" or you can do what I feel is a more natural feeling conversion to create an "Array of Structures".

To begin with, let's cover how you can access a query object as a structure. Once you see this, you might realize that no conversion is needed at all. The ColdFusion query object can be accessed directly like a structure of arrays:

  • <cfset strValue = qRecords[ COLUMN_NAME ][ ROW_NUMBER ] />

The first index is the key value of the column you want to access, for example, "id" or "name". The second index value is the row for which you want to access that column. If you want to see that in english, think of it as "I want the value in the COLUMN_NAME column of the ROW_NUMBER row. After seeing this, you might not want to go through the processing overhead of converting the query object into any other structure as it is pretty awesome to start with.

After this, if you still want to convert the query object in to another structure, I suggest going to an array of structures. This adds memory overhead (over the structure of arrays method) since each row must have a copy of the column names (as look-up keys), but I think it is a much more natural way of thinking about the query set (especially if you are a traditional programmer).

In order to convert a query into an array, you basically have to create an array, loop over every row in the query record set, create a structure of the values in that row, and then append that structure to the array:

  • <cffunction name="QueryToArray" access="public" returntype="array" output="false"
  • hint="This turns a query into an array of structures.">

  • <!--- Define arguments. --->
  • <cfargument name="Data" type="query" required="yes" />

  • <cfscript>

  • // Define the local scope.
  • var LOCAL = StructNew();

  • // Get the column names as an array.
  • LOCAL.Columns = ListToArray( ARGUMENTS.Data.ColumnList );

  • // Create an array that will hold the query equivalent.
  • LOCAL.QueryArray = ArrayNew( 1 );

  • // Loop over the query.
  • for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){

  • // Create a row structure.
  • LOCAL.Row = StructNew();

  • // Loop over the columns in this row.
  • for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){

  • // Get a reference to the query column.
  • LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];

  • // Store the query cell value into the struct by key.
  • LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];

  • }

  • // Add the structure to the query array.
  • ArrayAppend( LOCAL.QueryArray, LOCAL.Row );

  • }

  • // Return the array equivalent.
  • return( LOCAL.QueryArray );

  • </cfscript>
  • </cffunction>

To test, let's set up a simple query using an ID column and a NAME column:

  • <!--- Set up the query for testing. --->
  • <cfset qTest = QueryNew( "id, name" ) />

  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "1" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "molly" />

  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "2" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "Sophia" />

  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "3" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "Stefie" />

  • <cfset QueryAddRow( qTest ) />
  • <cfset qTest[ "id" ][ qTest.RecordCount ] = "4" />
  • <cfset qTest[ "name" ][ qTest.RecordCount ] = "Maud" />

Now, to convert that query to an array of structures, we simple call the QueryToArray() method and pass in the query as an argument.

  • <!--- Convert the query to an array. --->
  • <cfset arrTest = QueryToArray( qTest ) />

Dumping out the array, you can clearly see that it is an array or structures. Each structure at each array index has keys correlating to the query column.

Tweet This Fascinating post by @BenNadel - Ask Ben: Converting a Query to an Array Thanks my man — you rock the party that rocks the body!


Reader Comments

Thank you for this.

"For starters, is it worth it?"
Yes, if you need to dynamically access the columns.
MyQuery[variables.col] unfortunately only works with Structs. :(

Reply to this Comment

Keeping up with 'Converting a Query to an Array' is tough nowadays...As part of the relaunch of their site, the folks at depressedpress.com (http://www.depressedpress.com) are presenting a series of CFML challenges. The first is converting a Query to an Array using only CFML as fast as you can. Merry Christmas of Poland!

Reply to this Comment

With regard to the comment:
MyQuery[variables.col] unfortunately only works with Structs. :(

Yes you can, but you need to suffix it with the row that you want, so it'd be MyQuery[variables.col][row]

If you're looping through the rows then just use the loop variable, or if you're inside a cfoutput or cfloop then just use currentRow.

Reply to this Comment

I was pulling my hair out trying to access a query column dynamically and saw this post. Thanks much for the tip!
It's funny how many years you can program CF and still find something new.

Reply to this Comment

THAT is exactly what i´m looking for! cool you are my daysaver, thank you very much for sharing its a great tutorial. merry xmas.

Reply to this Comment

Yes, if you need to dynamically access the columns.

Couldn't one also use the evaluate() function to dynamically access the columns? E.g.,

<cfset temp = evaluate("MyQuery.#variables.col#[#index#]")>

IIRC, inside a CFLOOP or CFOUTPUT of MyQuery one would only need to do the following:

<cfset temp = evaluate("#variables.col#")>

I actually prefer building queries (using queryNew(), queryAddRow(), etc.) to using arrays of structures or structures of arrays because queries are so powerful.

Reply to this Comment

@Stju,

Did you actually test this? I ask because there is a fatal flaw in it - you are using the same Row struct for every row. Since Structs are passed by reference, every subsequent update you make to it will change EVERY row already added to the array.

I just copy-pasted your code to confirm this. When I CFDump, every row is the same as the last one.

I am going to delete your comment so as not to mis-inform people. I appreciate the effort though.

Reply to this Comment

What about using the built in listToArray and ValueList functions?

myArray = ListToArray( valueList(myQuery.columnName) )

If there is any chance your values could contain a comma, then use a different delimiter:

myArray = ListToArray( valueList(myQuery.columnName,Chr(31) ),Chr(31) )

Reply to this Comment

@Ryan,

You could do that; but, that organizes the arrays column-wise, not row-wise. Not saying that's a problem - just pointing out the difference.

Reply to this Comment

Not sure if anyone knows but to access dynamic column names you can do something similar to the following (quote and pound the dynamic column name as first array type element of query):

  • <cfset var cnt = 0>
  • <cfloop query="qry">
  • <cfset cnt++>
  • <cfloop from="1" to="#listLen(myDynamicFieldList)#" index="i">
  • cfset myDynamicColumn = qry["#listGetAt(myDynamicFieldList, i)]#"][cnt]>
  • </cfloop>
  • </cfloop>

Reply to this Comment

I recently encountered an issue where I needed to supply a case sensitive field name (DT_RowId) to jQuery DataTables plugin. The field names I got from using the QueryToArray function were all uppercase because ColdFusion internally stores struct keys in uppercase. To get around, change the following:

  • LOCAL.Columns = ListToArray(ARGUMENTS.Data.ColumnList );

to

  • LOCAL.Columns = data.getMetaData().getColumnLabels();

Hope this helps anyone who run into this issue.

References:

http://blog.pengoworks.com/index.cfm/2011/3/3/Easy-AJAX-using-ColdFusion-jQuery-and-CFCs

http://existdissolve.com/2010/11/quick-coldfusion-goodness/

http://datatables.net/release-datatables/examples/server_side/ids.html

Reply to this Comment

Thanks for sharing this. I would change
This line from

  • LOCAL.Row[ QtA.ColumnName ] = LOCAL.Qry[ LOCAL.ColumnName ][ LOCAL.RowIndex ];

To

  • StructInsert( LOCAL.Row, LOCAL.ColumnName, LOCAL.Qry[ LOCAL.ColumnName ][ LOCAL.RowIndex ] );

To allow better control of the case values when working with JSON/Javascript.

Reply to this Comment

Thank for the tip, I use it for refactor my code and paginate my Query, Thanks a lot

MyQuery.RecordCount make my Afternoon !!!

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.