Referring To The Proper Row Of The Outer CFLoop (With Nested CFLoops) : Kinky Solutions : A Student's Perspective by Ben Nadel

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

Posted February 23, 2007 at 12:15 PM

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:

Launch code in new window » Download code as text file »

<cfset qAlpha = QueryNew( "" ) />
<cfset QueryAddColumn(
qAlpha,
"letter",
"CF_SQL_VARCHAR",
ListToArray( "1,2,3,4,5" )
) />
<cfset qNumeric = QueryNew( "" ) />
<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:

Launch code in new window » Download code as text file »

<cfloop query="qAlpha">
<cfloop query="qNumeric">
#qAlpha.letter# -
#qNumeric.number#
</cfloop>
</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

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:

Launch code in new window » Download code as text file »

<cfloop query="qAlpha">
<cfloop query="qNumeric">
#qAlpha[ "letter" ][ qAlpha.CurrentRow ]# -
#qNumeric.number#
</cfloop>
</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).

Download Code Snippet ZIP File

Related Blog Entries

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

jQuery Custom Selectors - Holy Cow That Is So Badass!

Sleeping A Thread Pauses The CFSetting Request Time Out

Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

Very useful post, I will keep this in mind! I have always just done the temp variable hack in the past.

Posted by Ryan Stille on Feb 23, 2007 at 1:11 PM

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.

Posted by Ben Nadel on Feb 23, 2007 at 1:29 PM

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.

Posted by Boyan on Feb 23, 2007 at 3:09 PM

If I can prevent just one head-wall-bashing session... then I have made a difference ;)

Posted by Ben Nadel on Feb 23, 2007 at 3:11 PM

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!

Posted by Michael Horne on Feb 23, 2007 at 3:47 PM

No problem Michael. Next time you are stuck, just shoot me an email, maybe I can help.

Posted by Ben Nadel on Feb 23, 2007 at 4:35 PM

Best post on the web for coding around CFMX nested loop bug.

Posted by Jim peterson on Mar 3, 2007 at 11:38 AM

Awww shucks :)

Posted by Ben Nadel on Mar 3, 2007 at 11:51 AM

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

Posted by Brandon on Mar 16, 2007 at 11:16 AM

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.

Posted by Mark on Jul 7, 2007 at 5:49 PM

@Mark,

You are not going mad :) Glad to help.

Posted by Ben Nadel on Jul 8, 2007 at 12:28 PM

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]>

<cfset myNewTeamId = staffMemberSelect["teamid"][staffMemberSelect.currentrow]>
</cfif>
</cfloop>
<cfoutput>
#peNullTeamSelect["episodeid"][peNullTeamSelect.currentrow]# / #myNewTeamId# 
</cfoutput>
</cfloop>

Thanks Ben!

Posted by Gavin Baumanis on Jan 22, 2008 at 7:00 PM

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

Posted by Ben Nadel on Jan 23, 2008 at 9:39 AM

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.

Posted by Gavin Baumanis on Jan 23, 2008 at 10:53 AM

@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 :)

Posted by Ben Nadel on Jan 23, 2008 at 11:00 AM

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>

Posted by Kelly Lucas on Mar 31, 2008 at 12:43 PM

@Kelly,

Good call. I never tried that. Thanks for the tip.

Posted by Ben Nadel on Apr 1, 2008 at 9:03 AM

Post Comment | Ask Ben

Referring To The Proper Row Of The Outer CFLoop (With Nested CFLoops) : Kinky Solutions : A Student's Perspective by Ben Nadel