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 CFinNC 2009 (Raleigh, North Carolina) with:

Ask Ben: Converting A Query To A Struct

By Ben Nadel on

Is there an easy way to convert a coldfusion query record to a structure?

As I covered in my blog entry about converting a ColdFusion query to an array, you might not want to even go that far. A lot of times people want to convert queries and query records to other objects because they don't realize that they can reference rows and columns of a query without having to loop over the entire query:

  • <cfset strCellValue = qData[ COLUMN_NAME ][ ROW_INDEX ] />

However, if you want to convert the entire query to an array or queries, take at look at my previous post. But, if you want to convert just a single query row into a structure, I will show you a modification of my QueryToArray() method. This one, QueryToStruct() can convert a query to an array OR a single record to a struct:

  • <cffunction name="QueryToStruct" access="public" returntype="any" output="false"
  • hint="Converts an entire query or the given record to a struct. This might return a structure (single record) or an array of structures.">

  • <!--- Define arguments. --->
  • <cfargument name="Query" type="query" required="true" />
  • <cfargument name="Row" type="numeric" required="false" default="0" />

  • <cfscript>

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

  • // Determine the indexes that we will need to loop over.
  • // To do so, check to see if we are working with a given row,
  • // or the whole record set.
  • if (ARGUMENTS.Row){

  • // We are only looping over one row.
  • LOCAL.FromIndex = ARGUMENTS.Row;
  • LOCAL.ToIndex = ARGUMENTS.Row;

  • } else {

  • // We are looping over the entire query.
  • LOCAL.FromIndex = 1;
  • LOCAL.ToIndex = ARGUMENTS.Query.RecordCount;

  • }

  • // Get the list of columns as an array and the column count.
  • LOCAL.Columns = ListToArray( ARGUMENTS.Query.ColumnList );
  • LOCAL.ColumnCount = ArrayLen( LOCAL.Columns );

  • // Create an array to keep all the objects.
  • LOCAL.DataArray = ArrayNew( 1 );

  • // Loop over the rows to create a structure for each row.
  • for (LOCAL.RowIndex = LOCAL.FromIndex ; LOCAL.RowIndex LTE LOCAL.ToIndex ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){

  • // Create a new structure for this row.
  • ArrayAppend( LOCAL.DataArray, StructNew() );

  • // Get the index of the current data array object.
  • LOCAL.DataArrayIndex = ArrayLen( LOCAL.DataArray );

  • // Loop over the columns to set the structure values.
  • for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE LOCAL.ColumnCount ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){

  • // Get the column value.
  • LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];

  • // Set column value into the structure.
  • LOCAL.DataArray[ LOCAL.DataArrayIndex ][ LOCAL.ColumnName ] = ARGUMENTS.Query[ LOCAL.ColumnName ][ LOCAL.RowIndex ];

  • }

  • }


  • // At this point, we have an array of structure objects that
  • // represent the rows in the query over the indexes that we
  • // wanted to convert. If we did not want to convert a specific
  • // record, return the array. If we wanted to convert a single
  • // row, then return the just that STRUCTURE, not the array.
  • if (ARGUMENTS.Row){

  • // Return the first array item.
  • return( LOCAL.DataArray[ 1 ] );

  • } else {

  • // Return the entire array.
  • return( LOCAL.DataArray );

  • }

  • </cfscript>
  • </cffunction>

This functions takes two arguments, the ColdFusion query and the row index of the record you want to convert to a structure. If you want to convert the entire query to an array of structures, just send in the query but do not send in a row index:

  • <!--- Convert the entire query to an array of structures. --->
  • <cfset arrGirls = QueryToStruct( qGirls ) />

If you want to convert just a single record to a structure, then pass in the row index as the second argument:

  • <!--- Convert the second record to a structure. --->
  • <cfset objGirl = QueryToStruct( qGirls, 2 ) />

Be aware that this function can return two types of data, an array (for an entire query) or a structure (for a single record). That is why the returntype attribute is set to "any". Again though, be sure you really want to add this kind of processing overhead. When possible, and when appropriate, just use the ColdFusion query object like it is an array of arrays.

Tweet This Groovy post by @BenNadel - Ask Ben: Converting A Query To A Struct Thanks my man — you rock the party that rocks the body!


Reader Comments

Ben, thanks for this post. I was just looking for something exactly like this for a project I am working on at the moment. This helped me save a couple of hours easily.
Cheers,
Kai

Reply to this Comment

Kai, always glad to help. If you ever need any other help, please feel free to contact me directly via the Ask Ben feature and I will try to get you a solution to your problem.

Thanks,
Ben

Reply to this Comment

Ben, thanks for this... you just saved me a solid couple hours of cutting and pasting, or alternatively trying to write something like this on my own!
Thanks again,Rick

Reply to this Comment

What about

<cfset myArray=ArrayNew(1)>

<cfloop query="someQuery">

<cfset myStruct = StructNew()>

<cfloop list="#columns#" index="i">
<cfset StructInsert(myStruct,i,Evaluate("someQuery.#i#"))>
</cfloop>

<cfset ArrayAppend(myArray,StructCopy(myStruct))>

</cfloop>

Reply to this Comment

<cffunction name="QueryToStruct" returntype="struct" output="false">
<cfargument name="query" type="query" required="true">
<cfset s = StructNew()>
<cfloop index="i" list="#ARGUMENTS.query.ColumnList#">
<cfset StructInsert (s, i, ARGUMENTS.query[i])>
</cfloop>
<cfreturn s>
</cffunction>

Reply to this Comment

A limitation with deserializeJSON() in CF8 is that it cant convert a json string that looks like a cf query back into a query object.

Instead it converts the query to a struct containing 2 keys, columns and data. Data is an array of rows. Each row is an array of data for each column.

<cfquery name="myQuery">
select 'a1' as 'a', 'b1' as 'b' union
select 'a2' as 'a', 'b2' as 'b'
</cfquery>

struct = deserializeJSON( serializeJSON( myQuery ) );

eg. struct = {
columns: [
"a",
"b"
],
data: [
["a1","b1"],
["a2","b2"]
]
}

Reply to this Comment

@Mike,

Personally, I don't even like the idea of passing queries around from system to another; they feel too much like specialized data types. I prefer to pass around more natural, universal structures like arrays and structs.

Reply to this Comment

thank you for this excellent walkthrough ben, this code really helped me in my current application. hope you have a great week.

Reply to this Comment

As usual your posting has helped me out again. I continue to come back time and again when I have really odd requests from clients and find that you've already done it and have posted a solution that works everytime. Thx again!

Reply to this Comment

Works great! Is there a way to not remove leading zeroes on a column value? I have some varchars which start with 0 and it is removing them. Thanks!

Reply to this Comment

@Chad,

Hmm, I am not sure there is anything you can do. Since ColdFusion is a typeless language, it tends to convert values back and forth as it sees fit. As such, it's probably stripping off the leading zeros thinking it's a number. I don't know of a great way to prevent this off-hand. I'll do some digging to see what I can come up with.

Reply to this Comment

@Chad,

Leading zeros aren't being stripped for me...

Can you give more details about your setup?
mysql, sql server, cf7, cf8, cf9 etc?

Reply to this Comment

@Mike,

I am running CF9 w/ Oracle 10g. I was able to get the leading zeros to work in another one of my functions by adding a space in front of a variable is it a number (isNumeric), then wrapping it a serializeJSON() before returning it from the function, so I need to figure out a way to serialize each array value (LOCAL.RowIndex in Ben's code above) when it is being created or try to serialize the entire array.

Reply to this Comment

Ben your first snippet
<cfset strCellValue = qData[ COLUMN_NAME ][ ROW_INDEX ] />
wont work unless you put quotes around the column name leastways in cf8 so
<cfset strCellValue = qData[ "COLUMN_NAME" ][ ROW_INDEX ] />
otherwise another useful post.
d

Reply to this Comment

@Dan,

It depends on whether or not the name of the column is in a variable. In my case, the column name was in a variable; as such, it didn't need to be quoted. However, if you were going to put in the actual column name, then yes absolutely, with bracket-notation, you will have to quote the name. Excellent point.

Reply to this Comment

If you want all columns, in their specified case and in their selected order, use this:

  • columns = arrayToList( myQuery.getMeta().getColumnLabels() );

getColumnLabels() returns a java string array (string[]), so if you want to use cf array methods on it, you'll need to bounce it through a listToArray(arrayToList()) or similar.

It even gives you the complete list of columns when you: select * from table.

Simply loop over the array and build a struct.

Reply to this Comment

hey Ben,

Thanks for the code. It's worked great except I've come across one problem. When I send it a query that has a string field that contains numbers with a leading zero, the code strips the leading zero and coverts the string to a number. Any suggestions for updating the code to prevent this conversion from happening. I've checked the query results and the query is returning a string. After sending the query to QueryToStruct(), the field is converted to a number minus the leading zero.

Thanks for the great site. I've learned a lot.

Reply to this Comment

I'm a big fan, and ever so grateful for the wealth of info you've provided here. Just wanted to let you know that you might want to put quotes around the COLUMN_NAME example like so...

<cfset strCellValue = qData[ 'COLUMN_NAME' ][ ROW_INDEX ] />

Reply to this Comment

I find this quick hack may work...
struct = DeserializeJSON( SerializeJSON( query ) );

Reply to this Comment

Also, cfscript query loop does it for each row (at least in cf10):

for(thisRow in query){
writeDump(thisRow);
}

To create an array of structures from a query object in cfscript:

arResult=[];
for(thisRow in query){
arrayAppend(thisRow);
}

or

arResult=[];
for(thisRow in query){
arResult[query.currentRow]=thisRow;
}

Reply to this Comment

you may have to initialize the structure in the array (did not test the code):

arResult=[];
for(thisRow in query){
arResult[query.currentRow]={};
arResult[query.currentRow]=thisRow;
}

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.