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 »
- <!--- 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:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <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).
Download Code Snippet ZIP File
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
jQuery Custom Selectors - Holy Cow That Is So Badass!
Older Post
Sleeping A Thread Pauses The CFSetting Request Time Out
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).
