Ask Ben: Converting a Query to an Array
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.
Want to use code from this post? Check out the license.
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. :(
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!
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.
Nice solution, thank you.
Nice solution, thank you.
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.
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.
Nice article. I used to count 1, 2, 3... but now I'm just counting 0, 1, 2... Thanks!
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.
Nice solution, thank you.
Nice article. I used to count 1, 2, 3... but now I'm just counting 0, 1, 2... Thanks!
Best answer I could find on the net on this topic. Thank you.
@Mitran,
Awesome :)
Thank you for this solution :-)
thanks a LOT for this tutorial, it helped me ;)
searched quite a bit until i something usefull.
Exellent script!
[code removed]
@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.
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) )
@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.
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):
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:
to
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
Thanks for sharing this. I would change
This line from
To
To allow better control of the case values when working with JSON/Javascript.
Gracias, Ben.
Thank for the tip, I use it for refactor my code and paginate my Query, Thanks a lot
MyQuery.RecordCount make my Afternoon !!!
Hey Ben,
I know this is a very old post but because it describes a common issue you should consider updating this post to incorporate Kamil Saiyed's suggestion which preserves the case of property names which is obviously very useful for web development.
All the best,