Unqualified columns in nested query cfloops

Jun 08

At one point or another, we’ve all seen nested cfloop structures that both go over queries. What exactly should be output in this case?

<cfloop query="Outer">
	<cfloop query="Inner">
		<cfoutput>#Outer.CurrentRow#, #Outer.b#<br/></cfoutput>
	</cfloop>
</cfloop>

What if I told you: it depends on what version of ColdFusion you are running, would that be enough to make you not code like that?

Prepend the code above with this snippet, save, and run it on your copy of CF:

<cfset Outer=QueryNew("b")>
<cfset QueryAddRow(Outer)><cfset QuerySetCell(Outer,"b",1)>
<cfset QueryAddRow(Outer)><cfset QuerySetCell(Outer,"b",2)>
<cfset Inner=QueryNew("b")>
<cfset QueryAddRow(Inner)>

The inner loop should only go around once for each time the outer loop goes around. You could make the inner loop longer, but it wouldn’t change anything.

The basic problem stems from the unqualified column name Outer.b and what you expect it to mean. Outside of a loop, Outer.b is really a shortcut for Outer.b[1] — that is, the first row of the query. But inside a loop? That’s where things get hinky.

 CF5CF7CF8
Iteration 1Outer.CurrentRow111
Outer.b111
Iteration 2Outer.CurrentRow122
Outer.b112

I don’t have a copy of CF6 handy, but I would imagine it has the same results as CF7. Interpreting this table, you get:

  • CF5 had a bug such that the outer loop’s CurrentRow never incremented. You’re basically screwed if you don’t always want Outer.b[1], as you can’t get Outer.b[Outer.CurrentRow] because Outer.CurrentRow is always 1 inside of a nested loop.
  • CF7 sees Outer.CurrentRow correctly, but interprets Outer.b inside of a loop just like it would outside of a loop: as Outer.b[1].
  • CF8 interprets Outer.b outside a loop as Outer.b[1], but inside a loop as Outer.b[Outer.CurrentRow].
    You could interpret this to mean that CF8 always translates it to Outer.b[Outer.CurrentRow], even outside of a loop, as Outer.CurrentRow will be 1 outside of a loop.

Given that you cannot rely on the shorthand notation for column names across versions, I would strongly recommend that you just don’t use it anymore, and in fact change it wherever you see it. Changing from Query.b to Query.b[1] when you want the first row explicitly only adds 3 characters. Changing it to Outer.b[Outer.CurrentRow] does add more characters, but there is absolutely no doubt as to what you were trying to accomplish.

Of course, many people will argue that they can no longer do things like <cfif Outer.b NEQ "">, which is a convenient way of testing if a query returned any rows. But you’re just going to run into bugs that way, so you may as well type out <cfif (Outer.RecordCount GT 0) AND (Outer.b NEQ "")>, because that’s really what you meant, right? And what happens when the next version of CF throws an error for trying to access the data for a query that has no rows? That’s just lazy.