ColdFusion 8's New Nested Query Loop Behavior (Thanks Rick Osborne)

Posted June 9, 2007 at 2:51 PM

Tags: ColdFusion

I was just reading over on Rick Osborne's blog that ColdFusion 8's nested query loop behavior is different than that of ColdFusion MX 7 (he also demonstrates ColdFusion 5... but come on - if you're still using ColdFusion 5, nested query loops is the least of your problems). I believe Rick, but this is something I just had to see for myself. To test this, I manually built two queries for my nested looping:

 Launch code in new window » Download code as text file »

  • <!---
  • Create the new query instance to keep track
  • of the girls.
  • --->
  • <cfset qGirl = QueryNew( "" ) />
  •  
  • <!---
  • Add a column to the query and assign it some
  • default values.
  • --->
  • <cfset QueryAddColumn(
  • qGirl,
  • "name",
  • "CF_SQL_VARCHAR",
  • ListToArray( "Kimmie,Samantha,Julia" )
  • ) />
  •  
  •  
  • <!---
  • Create the new query instane to keep track of
  • the body parts that the girls might have.
  • --->
  • <cfset qPart = QueryNew( "" ) />
  •  
  • <!---
  • Add a column to the query and assign it some
  • default values.
  • --->
  • <cfset QueryAddColumn(
  • qPart,
  • "name",
  • "CF_SQL_VARCHAR",
  • ListToArray( "Head,Hips,Butt,Feet" )
  • ) />

Then, I looped over the qGirl query to output their names and for each girl, I then had a nested query loop that output their body parts:

 Launch code in new window » Download code as text file »

  • <!---
  • Loop over the girl query and for each girl,
  • output the body parts that the girl has.
  • --->
  • <cfloop query="qGirl">
  •  
  • <h2>
  • #qGirl.name#
  • </h2>
  •  
  • <p>
  • <cfloop query="qPart">
  •  
  • #qGirl.name# has #qPart.name#<br />
  •  
  • </cfloop>
  • </p>
  •  
  • </cfloop>

First, I ran the above code on our ColdFusion MX 7 development server and got this output:

Kimmie

Kimmie has Head
Kimmie has Hips
Kimmie has Butt
Kimmie has Feet

Samantha

Kimmie has Head
Kimmie has Hips
Kimmie has Butt
Kimmie has Feet

Julia

Kimmie has Head
Kimmie has Hips
Kimmie has Butt
Kimmie has Feet

As you can see, while the inner loop iterates properly over its body parts, the outer loop (from inside the inner loop) will only refer to the first record of the inner loop. This is a known behavior that can be dealt with by using the desired row number of the outer loop (qGirl[ "name" ][ qGirl.CurrentRow ]).

Then, I uploaded this to my ColdFusion 8 development account and ran it. ColdFusion 8 gives me this output:

Kimmie

Kimmie has Head
Kimmie has Hips
Kimmie has Butt
Kimmie has Feet

Samantha

Samantha has Head
Samantha has Hips
Samantha has Butt
Samantha has Feet

Julia

Julia has Head
Julia has Hips
Julia has Butt
Julia has Feet

As you can see, qGirl.name, even when part of the inner query loop was referring to the proper row of the outer query loop. This is a very different change in query looping behavior. I would have to agree with what Rick says about just not using this type of query referencing behavior anymore.

Thanks for pointing that out Rick.

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page




Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

Jun 9, 2007 at 6:08 PM // reply »
149 Comments

It's interesting to see the kind of holy war this generally leads to. One camp wants Query.Column to always mean Query.Column[1], even in a loop, because it is consistent and predictable -- the CF7 route. The other camp wants it to be Query.Column[Query.CurrentRow], like CF8, so that it's more intuitive (for them, anyway) inside of loops. I can see it both ways, but I'm amazed at how quickly people get dragged down into the mud on this one.

I think Adobe ought to keep changing it back and forth, not just with major releases, but with randomly-timed hotfixes and service packs. At least then maybe people wouldn't count on it happening one way, thus locking their code into only one version of CF.


Jun 9, 2007 at 6:56 PM // reply »
5,406 Comments

Ha ha ha, that would be funny - to see it change with random hot fixes.

After I posted this, I checked to see what would happen if you nested the same query twice:

<cfloop query="qGirl">
<cfloop query="qGirl">
#qGirl.name#
</cfloop>
</cfloop>

This works the same in CF7 and CF8. qGirl.name refers to the inner loop and the outer loop serves only to iterate a set number of times (once per girl).


Jun 11, 2007 at 9:51 AM // reply »
6 Comments

I think it is good that Adobe fixed that "feature" (read bug) whcih has been in CF too long. Of course, this is just the subtle type of butt biting change that can make that one important page of your site start behaving differently after you upgrade and you JUST can't figure it out... haha.

Know thy code.

~Brad


Post Comment  |  Ask Ben

Recent Blog Comments
Secret Admirer
Jul 4, 2009 at 12:23 PM
Project HUGE: Huge In A Hurry - Get Big - Phase 2 / Week 3
My Poor Dreamboat :( I feel so sad when I know you are hurting. I hope you feel better soon. ... read »
Jul 4, 2009 at 9:42 AM
FLV 404 Error On Windows 2003 Server
I bookmarked this page. Thanks for given this great post.... ... read »
Jul 4, 2009 at 4:00 AM
Terms Of Service / Privacy Policy Document Generator
thanks ben, I'm not a big fan of contracts so to find your no no-nesense ToS generator has helped me no end. all the best matt ... read »
Justice
Jul 3, 2009 at 11:10 PM
Create A Running Average Without Storing Individual Values
@Ben, I think you're going about this the wrong way. You're trying to use complicated techniques when there is a simple and beautiful technique readily available (a la Gary Funk's comment). Instead ... read »
Bob
Jul 3, 2009 at 9:19 PM
Project HUGE: Huge In A Hurry - Get Big - Phase 3 / Week 1
a good technical explanation http://crossfitphoenix.typepad.com/crossfit_phoenix_forging_/the-overhead-squat.html ... read »
Jul 3, 2009 at 9:03 PM
Create A Running Average Without Storing Individual Values
If I wanted to do this and only carry two numbers, I'd keep track of the sum and N. Then you are pretty much accurate all the time. average = (sum + new_number) / (N + 1) But all this was in a for ... read »
Roland Collins
Jul 3, 2009 at 8:58 PM
Create A Running Average Without Storing Individual Values
@Martin - not just floating point though. Depending on what langauge you're working in, decimals can cause just as many headaches if they're not precise enough. But again, for most applications, th ... read »
Isnogood
Jul 3, 2009 at 7:16 PM
Project HUGE: Huge In A Hurry - Get Big - Phase 3 / Week 1
Watch this http://www.nsca-lift.org/videos/default.shtml ... read »