Ask Ben: Converting A Query To A Struct
Posted July 19, 2006 at 7:32 AM by Ben Nadel
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.
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
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
Ben, thanks I was looking for this very same thing.
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
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>
<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>
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"]
]
}
@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.
thank you for this excellent walkthrough ben, this code really helped me in my current application. hope you have a great week.
@Chris,
Glad to help!
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!
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!
@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.
@Chad,
Leading zeros aren't being stripped for me...
Can you give more details about your setup?
mysql, sql server, cf7, cf8, cf9 etc?
@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.
Ben, Thank you for creating this module. You are the BEST. The Superman of Coldfusionopolis. JD
@Jusuf,
Ha ha, thanks for that :)
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
@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.
Anybody have a version of this that will preserve the case of the column names?
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.
How would you use cfloop to loop over returned data?
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.
You should add this to http://www.cflib.org
Was a great help.
Paired with jsonencode your function was compatible with YUI's DataSource Data Structure!
I can't thank you enough!
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 ] />
