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

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 HeadKimmie has HipsKimmie has ButtKimmie has Feet

Samantha

Kimmie has HeadKimmie has HipsKimmie has ButtKimmie has Feet

Julia

Kimmie has HeadKimmie has HipsKimmie has ButtKimmie 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 HeadKimmie has HipsKimmie has ButtKimmie has Feet

Samantha

Samantha has HeadSamantha has HipsSamantha has ButtSamantha has Feet

Julia

Julia has HeadJulia has HipsJulia has ButtJulia 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.

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.

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.