ColdFusion 8's New Nested Query Loop Behavior (Thanks Rick Osborne)
Posted June 9, 2007 at 2:51 PM by Ben Nadel
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:
- <!---
- 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:
- <!---
- 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.
Reader 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.
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).
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
