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

Posted February 23, 2007 at 12:15 PM

Tags: ColdFusion

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 »

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 »

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:

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

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

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

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

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.

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.

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

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!

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

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

Awww shucks :)

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

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.

@Mark,

You are not going mad :) Glad to help.

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!

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

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.

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

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>

@Kelly,

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

This is exactly what I needed. Thanks!

thank you! thank you! thank you!

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

@Laker,

You'll be happy to know, at least, that this bug was fixed in ColdFusion 8 (as far as I can remember).

Hmmm. I'm running CF 8.0.1 (Standard) and am definitely experiencing this issue :)

Laker

I was going to say the same thing!
It's definitely is still a problem with CF8.

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

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.