Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with:

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

By Ben Nadel on
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:

  • <!--- 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).




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

Reply to this Comment

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.

Reply to this Comment

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!

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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!

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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>

Reply to this Comment

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

Reply to this Comment

@Laker,

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Once again, Ben, you have saved me. Could not figure out this issue for the life of me. Thanks.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.