Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Amsterdam) with: Terrence Ryan and David Huselid and Alison Huselid and Claude Englebert

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

By Ben Nadel on
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:

  • <!---
  • 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.



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

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.

Reply to this Comment

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).

Reply to this Comment

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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.