Skip to main content
Ben Nadel at the New York ColdFusion User Group (Feb. 2008) with: Clark Valberg and Ray Camden and Judith Dinowitz
Ben Nadel at the New York ColdFusion User Group (Feb. 2008) with: Clark Valberg ( @clarkvalberg ) Ray Camden ( @cfjedimaster ) Judith Dinowitz

Referring To The Proper Row Of The Outer CFLoop (With Nested CFLoops)

By on
Tags:

Andy Matthews over on CF-Talk just asked why references to outer CFLoop columns within his inner CFLoop always refer to the first record. Jim Wright appropriately responded:

"because that is the way it is".

It's true. It has been like that for a while. I don't know why. I assume it has something to do with the scope context of a loop. The context references just get confused. However, while the short-hand references (ex. query.value) of the original query (of the outer CFLoop) get confused, that doesn't mean the original query is not maintaining its own state properly.

To demonstrate, let's build two simple queries:

<!--- Build an Alpha query. --->
<cfset qAlpha = QueryNew( "" ) />

<!--- Populate that query with a single column of letters. --->
<cfset QueryAddColumn(
	qAlpha,
	"letter",
	"CF_SQL_VARCHAR",
	ListToArray( "1,2,3,4,5" )
	) />


<!--- Build a numeric query. --->
<cfset qNumeric = QueryNew( "" ) />

<!--- Populate that query with a single column of numbers. --->
<cfset QueryAddColumn(
	qNumeric,
	"number",
	"CF_SQL_VARCHAR",
	ListToArray( "A,B,C,D,E" )
	) />

Ok, now, let's nest the loops in a manner that we would expect them to work:

<cfloop query="qAlpha">

	<p>
		<cfloop query="qNumeric">

			#qAlpha.letter# -
			#qNumeric.number#<br />

		</cfloop>
	</p>

</cfloop>

Here, we would expect the qAlpha.letter value to increment once for each of the outer loops... but in fact, this is the output we get:

1 - A
1 - B
1 - C
1 - D
1 - E

1 - A
1 - B
1 - C
1 - D
1 - E

1 - A
1 - B
1 - C
1 - D
1 - E

1 - A
1 - B
1 - C
1 - D
1 - E

1 - A
1 - B
1 - C
1 - D
1 - E

As you can see, the outer loop short hand references get confused. Again, this is just the way it is. BUT, the outer query loop is still working properly (it loops five times). And, in fact, the query object / iterator is still maintaining the proper state when inside the nested query loop.

To demonstrate this, we can refer to the outer loop's CurrentRow rather than its value short hands:

<cfloop query="qAlpha">

	<p>
		<cfloop query="qNumeric">

			#qAlpha[ "letter" ][ qAlpha.CurrentRow ]# -
			#qNumeric.number#<br />

		</cfloop>
	</p>

</cfloop>

Notice that instead of using qAlpha.letter, we are using qAlpha[ "letter" ][ qAlpha.CurrentRow ]. These are the exact same thing, the latter is just less convenient to code. This gives us the output:

1 - A
1 - B
1 - C
1 - D
1 - E

2 - A
2 - B
2 - C
2 - D
2 - E

3 - A
3 - B
3 - C
3 - D
3 - E

4 - A
4 - B
4 - C
4 - D
4 - E

5 - A
5 - B
5 - C
5 - D
5 - E

As you can see, the outer loop is indeed looping and maintaining its state properly.... it's just a matter of knowing how to reference it properly (or rather in a way that works).

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/go/546

Reader Comments

15,674 Comments

Ryan,

No problem. I think we alllll go the temp variable route at first. Certainly, it's a great choice, especially if you only have to reference one or two values and you want to create short-hands for them.

95 Comments

I tend to forget this at time and I want to hit my head against the wall until I finally remember...duh!...I'm such an idiot, he he. Thanks for the reminder.

10 Comments

Good Lord! I'm sure this must have been blogged by someone before, but I've never seen it. The amount of time I must've spent because of this strangeness...

Thank you!

5 Comments

Thank you, thank you, thank you. I just encountered this and thought I must be going totally insane.

1 Comments

Thank you. Thank you. And, thank you.

The way CF handles nested query loops isn't intuitive, but I understand that a fix is in the works. Be that as it may, thank you for figuring this one out. I thought I was going mad.

3 Comments

Hi Everyone,

It took me a few goes to get it working for me...
But I did manage to get it working.
The problem for though was that it was the inner loop that wouldn't update.

By using Ben's [....currentrow] syntax in ALL places where a query variable was used I managed to fix my problem.

<cfloop query="peNullTeamSelect">
<cfloop query="staffMemberSelect">
<cfif staffMemberSelect["dispname"][staffMemberSelect.currentrow] eq peNullTeamSelect["dispname"][peNullTeamSelect.currentrow]>
<!--- update the team id --->
<cfset myNewTeamId = staffMemberSelect["teamid"][staffMemberSelect.currentrow]>
</cfif>
</cfloop>
<cfoutput>
#peNullTeamSelect["episodeid"][peNullTeamSelect.currentrow]# / #myNewTeamId#<br />
</cfoutput>
</cfloop>

Thanks Ben!

15,674 Comments

@Gavin,

Glad you got it working. There's nothing wrong with referring to CurrentRow in conjunction with the staffMemberSelect query; however, because it is the inner loop, you don't have to use the CurrentRow. You only need to use CurrentRow when referring to the outer loop from within the inner loop.

3 Comments

Hi Ben,
I don't know whether it is an implementation issue or not;
I.e. the output is in the outer loop and not the inner one - but without the currentrow reference in the inner loop, the results were incorrect.

I certainly don't want to come across as a smart-arse either - but in your example, did you JUST correct the names of your queries?

I could have sworn that previously your queries were named inversely.

I.e. query alpha - contained numbers.

Regardless of all that - by using your example, I was able to successfully complete the updates I was having such a shit of a time - to get working.

Gavin.

15,674 Comments

@Gavin,

I am not in front of a computer that has ColdFusion, so I cannot test anything, so you might be absolutely correct. The important thing is that you got it working :)

1 Comments

Maybe it goes without saying, but it's worth noting that this CF7 behavior also occurs if you do:
<cfloop query="outerquery">
<cfmail query="innerquery" ... >#outerquery.somefield#</cfmail>
</cfloop>

2 Comments

I have discovered two interesting (read "annoying") items regarding this bug (it is a bug isn't it?)...

1) In the code I have been writing I am using "columnList" to copy a subset of records in one query to a new query. The "temp var" hack is still needed IF you are using the query value obtained as the parameter of another function. Here is an example:
<cfset output = queryNew(category.items.columnList) />
<cfloop query="category.items" startrow="#start#" endrow="#end#">
<cfset queryAddRow(output) />
<!--- Inner loop copies values from current row of outer loop to new query row --->
<cfloop index="i" from="1" to="#ListLen(category.items.columnList)#">
<cfset curCol = category.Items[ListGetAt(category.items.columnList,i)] />
<cfset querySetCell(output,ListGetAt(category.items.columnList,i),curCol) />
</cfloop>
</cfloop>

If I replace "curCol" in querySetCell with the actual expression I am assigning to "curCol" on the line prior to the querySetCell the outer loop always returns record 1, even using array notation.

2) There also appears to be no solution if you are using cfscript to loop through the queries, i.e, the outer loop always points to record/row 1.
Example 2:
output = queryNew(category.items.columnList);
for (j=start;j lte end; j+=1) {
queryAddRow(output);
for (i=1;i lte ListLen(category.items.columnList); i+=1) {
curCol = category.Items[ListGetAt(category.items.columnList,i)];
querySetCell(output,ListGetAt(category.items.columnList,i),curCol);
}
}

Of course I had to go through all of this misery before finding your post 8-| ...but at least I know it's not just me ;-)

Laker

15,674 Comments

@Gavin, @Laker,

I just tested this and it seems to be working fine:

<cfset q1 = queryNew("") />
<cfset queryAddColumn( q1, "id", "varchar", listToArray( "1,2,3,4" ) ) />

<cfset q2 = queryNew("") />
<cfset queryAddColumn( q2, "name", "varchar", listToArray( "Sarah,Molly,Joanna,Carolyn" ) ) />

<cfloop query="q1">
<cfloop query="q2">
#q1.id# - #q2.name#<br />
</cfloop>
<br />
</cfloop>

... outputs:

1 - Sarah
1 - Molly
1 - Joanna
1 - Carolyn

2 - Sarah
2 - Molly
2 - Joanna
2 - Carolyn

3 - Sarah
3 - Molly
3 - Joanna
3 - Carolyn

4 - Sarah
4 - Molly
4 - Joanna
4 - Carolyn

What kind of looping are you using? I am running ColdFusion 8 (not sure which sub-version).

1 Comments

Ben,

Thank you for all of the tips. However, I suggest you change your database so that the inner query has different records for each of the records in the outer query. This way, you will see (I believe) what Gavin was talking about. I also am seeing what Gavin describes - for every inner query, I always return the same records (those for the first outer query record). I've tried Gavin's suggested solution, but so far I can't get anything back from the inner query except the rows for the first outer query record. I am using CF9, FWIW.

Dean

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel