Using The ColdFusion Query Object As A Complex Object Iterator
Posted August 23, 2006 at 2:31 PM by Ben Nadel
Let me preface this by saying this is probably not useful, but kind of sweet anyway. Did you know that you can store complex data inside of a query object? It's true; you can pretty much store any kind of data inside of a ColdFusion query object. To demonstrate that, let's try to use the query object as an iterator of complex data structures. This, in and of itself, could be kind of cool, because the query object has some methods that are nice for iteration.
First, let's build a query from scratch with a single column that houses structures:
- <!---
- Create a query with only data column to hold
- out complex objects.
- --->
- <cfset qGirls = QueryNew( "data" ) />
-
- <!--- Add rows to query. --->
- <cfset QueryAddRow( qGirls, 3 ) />
-
- <!--- Set row data. --->
- <cfset qGirls[ "data" ][ 1 ] = StructNew() />
- <cfset qGirls[ "data" ][ 1 ].Name ="Thomas, Ashley" />
- <cfset qGirls[ "data" ][ 1 ].IsSexy = true />
- <cfset qGirls[ "data" ][ 1 ].HowSexy = "Very" />
-
- <!--- Set row data. --->
- <cfset qGirls[ "data" ][ 2 ] = StructNew() />
- <cfset qGirls[ "data" ][ 2 ].Name ="Fisakis, Anne" />
- <cfset qGirls[ "data" ][ 2 ].IsSexy = true />
- <cfset qGirls[ "data" ][ 2 ].HowSexy = "Very" />
-
- <!--- Set row data. --->
- <cfset qGirls[ "data" ][ 3 ] = StructNew() />
- <cfset qGirls[ "data" ][ 3 ].Name ="Cox, Christina" />
- <cfset qGirls[ "data" ][ 3 ].IsSexy = true />
- <cfset qGirls[ "data" ][ 3 ].HowSexy = "Very" />
As you see, for each query row, I am setting the value of [data] to a StructNew() and then treating the value as a struct. If you dump this out, you will see that everything is fine and dandy:
| | | | ||
| |  | | ||
| | | |
Now, let's loop over it and output the data values:
- <!--- Loop over girls. --->
- <cfloop query="qGirls">
-
- <!--- Get current girl. --->
- <cfset objGirl = qGirls.data />
-
- <!--- Output data. --->
- #objGirl.Name#<br />
- #objGirl.IsSexy#<br />
- #objGirl.HowSexy#<br />
-
- <!--- Check for break. --->
- <cfif NOT qGirls.IsLast()>
- <br />
- </cfif>
-
- </cfloop>
Works as you would *hope* it to. Well, that's not 100% true. There are some caveats here. The biggest one is that you CANNOT reference the struct directly as in:
- <cfset strName = qGirls.data.Name />
This throws the error:
Element DATA.NAME is undefined in QGIRLS
That is lame as that is what you would want to do naturally. To overcome this, you have two options: you can either do what I did above, by creating a intermediary "objGirl" structure, then referencing that, or you can use structure notation as in:
- <cfset strName = qGirls[ "data" ][ 1 ].Name />
So that is a not-great caveat, but notice, on the other hand, that we can leverage other great query features like IsFirst() and IsLast(). No more comparing the current index to the array length (or however else you would do this sort of loop). Plus, you can use things like StartRow and EndRow:
- <!--- Loop over first 2 rows. --->
- <cfloop query="qGirls" startrow="1" endrow="2">
- ....
- </cfloop>
So, like I said, maybe not useful, but a kind of down-and-dirty way to use the query object. Plus, I am sure I am not alone on this, but isn't the query loop much nice to look at than an index loop?
Reader Comments
Scary timing. I was just yesterday coding up a plugin-management CFC base-class/factory that uses objects inside of queries.
And yeah, cfloop with a query is so much sexier than without.
Yeah, Rick, that's what i'm talking about! Sexy!
Actually Ben your post was extremely helpful to me. I don't know if you use the report builder, but in that you are allowed to pass information from only one query into the report (not including any additional parameters passed in when the report is called). Anyway, before I read your post I had been imbedding subreports within subreports to get an output that was passable, but not great. Now I can pass all kinds of information along in the query by imbedding them in structures. Thanks!
Cameron,
I have to say, I have only studies the report builder, but never actually used it. I am glad that this helps though. I am aware that you can pass queries into reports at run time, but passing a query of structs is wild :) Rock on.
You player, you!
