ColdFusion 10 - Looping Over Queries Using A For-In Loop In CFScript
Posted April 19, 2012 at 9:35 AM by Ben Nadel
Yesterday, I campaigned against using ColdFusion 10's dynamic "query" attribute in the CFLoop tag. That said, I am really excited that CFScript now supports FOR-IN looping for queries. This creates complete uniformity in CFScript for looping over queries, arrays, and structs. And, it definitely makes looping over queries in CFScript much easier and far more intuitive than the index-based looping that was previously required.
NOTE: At the time of this writing, ColdFusion 10 was in public beta.
To quickly demonstrate this new features, I am going to build up a new query and then loop over it using a FOR-IN loop. In the following code, you'll notice that you can still access query meta-data (recordCount and currentRow) using the query object:
- <cfscript>
-
-
- // Build up a new query. Notice that we are using the augmented
- // features of ColdFusion 10 and the queryNew() to build complex
- // queries in one command.
- friends = queryNew(
- "id, name",
- "cf_sql_integer, cf_sql_varchar",
- [
- [ 1, "Tricia" ],
- [ 2, "Sarah" ],
- [ 3, "Joanna" ]
- ]
- );
-
-
- // The query object now supports FOR-IN iteration in CFScript. We
- // can iterate over the query, row by row.
- for (friend in friends){
-
- // When iterating over a query in CFScript, you can use the
- // main query object to get meta-data; then, use the row
- // object to get row-specific properties.
- writeOutput(
- "[ #friends.currentRow# of #friends.recordCount# ] " &
- friend.name &
- "<br />"
- );
-
- }
-
-
- </cfscript>
As you can see, each row of the query is presented as a struct in which the column names have become struct keys. This makes looping and outputting query data very easy! When we run the above code, we get the following output:
[ 1 of 3 ] Tricia
[ 2 of 3 ] Sarah
[ 3 of 3 ] Joanna
With this level of ease, I can't see much of a use for ever converting a query object to another kind of object (except maybe for with API return data). But, with the way query-iteration now works, you can see that converting a query object to an array-of-structs can be incredibly straightforward - the query iteration is already doing have the work for you:
- <cfscript>
-
-
- // Build up a new query. Notice that we are using the augmented
- // features of ColdFusion 10 and the queryNew() to build complex
- // queries in one command.
- friends = queryNew(
- "id, name",
- "cf_sql_integer, cf_sql_varchar",
- [
- [ 1, "Tricia" ],
- [ 2, "Sarah" ],
- [ 3, "Joanna" ]
- ]
- );
-
-
- // I convert the given query to an array of structs.
- function queryToArray( query ){
-
- // Define our array to hold the row data for the query.
- var queryAsArray = [];
-
- // Iterate over the query using a FOR-IN construct. This will
- // automatically convert each row to a struct. At that point,
- // all we have to do is collect it.
- for (var row in query){
-
- // Add the row-as-struct to our array.
- arrayAppend( queryAsArray, row );
-
- }
-
- // Return the query collection as an array.
- return( queryAsArray );
-
- }
-
-
- // Iterate over the query as an array. Since we are using the
- // arrayEach() method, we lose track of the current row and
- // the recordCount.
- arrayEach(
- queryToArray( friends ),
- function( friend ){
-
- // Here, each "friend" is the struct produced by the
- // FOR-IN iteration above.
- writeOutput( friend.name & "<br />" );
-
- }
- );
-
-
- </cfscript>
Here, we've created a function - queryToArray() - that simply collects the structs produced by each row-iteration and appends them to an array. Then, we iterate over the array using ColdFusion 10's new arrayEach() method and get the following output:
Tricia
Sarah
Joanna
As I said before, ColdFusion 10 is really the version of ColdFusion that makes CFScript look really appealing to me. Up until now, I've been a strong proponent of ColdFusion tags for everything; but, CFScript is really maturing to the point where I can imagine using it on a regular basis.
Reader Comments
Hey Ben...you've got a typo in the first para. "perviously required".
We agree on this one :)
Great addition.
I love using the for-in clause now in cfscript! Except when looping over component metadata.
Silly things claim to be arrays when they aren't really, and thus break for-in, at least in cf9, haven't tried 10.
@Andy,
Ah, good catch! Should be fixed upon next cache-clear.
@Sam,
Ha ha, high-five :)
@Jim,
Yeah, I'm definitely loving the uniformity now of the script-based looping. That's really weird about the metadata, though. Maybe it's some weird Java object that is confusing the engine? Very strange.
@Ben
Yeah, when using getComponentMetaData() or getMetaData(), it treats parameters as an an array during a writeDump() (possibly functions as well, i forget now), and you can iterate over them with a normal for loop. But when you try to use for-in, it throws something about trying to reference a complex object as a scalar or something and lists it as a java object.
As an aside since it's related, I'm using railo as a place holder until cf10 comes out. Been going through and converting cfcs to cfscript only versions, using your example for metadata in the /** **/ blocks.
Railo doesn't support metadata (or the accessors flag, or a bunch of other things) in those blocks and forces me list them in the method declaration. According to bug reports, Railo doesn't seem inclined to support this functionality due to semi-justifiable language politics, but that argument falls flat when you look at how C# does webmethods.
Was having a fun time with Railo too until I hit this, now i'm dumping it into waste bin of "meh" could-have-beens. Guess thats what I get for experimenting.
CF10 needs to hurry up and drop so I can spend budget monies on it!
@Ben
Might want to double check your code for sending out comment posts, getting a profile pic that is clearly not your font of manliness on your posts.
@Jim,
"But when you try to use for-in, it throws something about trying to reference a complex object as a scalar or something and lists it as a java object"
99% sure that is fixed in CF10.
Its an annoying bug for sure.
@Jim,
Sounds really strange. Out of curiosity, what are you using the metadata for? Sounds like you're really digging into it.
@Sam,
That's good to hear!
@Ben
I'm updating my ExtDirect stack that supports persistent namespaces for invoking components to the latest ExtDirect spec that supports named arguments.
Thus, as I'm adding components to my registry, instead of just counting the number of required arguments, I'm now tracking named optional and required arguments so that when named arguments are used by ExtDirect, I can ensure the argument collection for requireds is fully populated or not, and handle accordingly.
Once I get that done, I'm adding support to the stack to prevent possible CSRF attacks from the AJAX side of ExtJS.
Nice.
Would be glad to see the "item" attribute added to CFLOOP QUERY in CF10. Than you have both: the for-in-loop as in script and you can use a dynamic QUERY attribute without scoping trouble ;-)
SSD Web Solutions provides domain
registration, web hosting and bulk sms services to small businesses. We
provide web hosting on both platform windows hosting and linux hosting. We
do also provide bulk voice calls for promotional calls to India. To find out more information, go visit: http://www.ssdindia.com/
