Ask Ben: Converting A Query To A Struct
Posted July 19, 2006 at 7:32 AM
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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <!--- 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:
Launch code in new window » Download code as text file »
- <!--- 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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
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!
