Possible bug: looping over queries within loops

[RobinWhitehead]

Don't know if this has been discussed already, but I am having problems with CF.

I have one cfquery that I run that returns a recordset. I am looping over this
using <cfloop query="queryName">. This happens fine.

The problem is, is when I put ANOTHER query inside this loop, and then loop
over that (again still inside the outer query loop), then inside the <cfloop>
tag for the inner query, it can only see the first record in the first dataset.
This is probably clearer if i demonstrate it.

First, create two SQL tables, say TestTable1 and TestTable2. Populate the
tables with arbitratry data:

TestTable1:
----------------
testKey1, data
1,12
2,34
3,45

TestTable2:
----------------
testKey2, data
1,67
2,78
3,34

Now run this code on the tables:
<cfquery name="getTestValues1" datasource="#ds#">
SELECT
data
FROM
testTable1
</cfquery>

<cfloop query="getTestValues1">
data in outerloop - <cfoutput>#getTestValues1.data#</cfoutput><br>

<cfquery name="getTestValues2" datasource="#ds#">
SELECT
data
FROM
testTable2
</cfquery>

<cfloop query="getTestValues2">
data in innerloop - <cfoutput>#getTestValues1.data#</cfoutput>
</cfloop>
<br>
</cfloop>

The results you get are this:
data in outerloop - 12
data in innerloop - 12 data in innerloop - 12 data in innerloop - 12
data in outerloop - 23
data in innerloop - 12 data in innerloop - 12 data in innerloop - 12
data in outerloop - 34
data in innerloop - 12 data in innerloop - 12 data in innerloop - 12

As you can see the value of #getTestValues1.data# in the outer loop is
correct, but in the inner loop, it is only ever returned as the first record in
its recordset.

Cheers,
Rob.

[Dan Bracuk]

One of your problems is here.
<cfloop query="getTestValues2">
data in innerloop - <cfoutput>#getTestValues1.data#</cfoutput>
</cfloop>

When you are in a loop or cfoutput from one query, and want to work with
another, you have to specify the row number in square brackets. So your code
has to resmble
<cfloop query="getTestValues2">
data in innerloop - <cfoutput>#getTestValues1.data[rownumber]#</cfoutput>
</cfloop>

How you get the rownumber is up to you.

[mxstu]

This behavior is described in this technote
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_17033[/url]

It is a good idea to avoid nesting cfquery's inside a loop, for performance
reasons. However, you could easily do this by using from/to loops.



<CFQUERY NAME="getTestValues1" DATASOURCE="#ds#">
SELECT Data FROM yourTable
</CFQUERY>

<cfloop from="1" to="#getTestValues1.recordCount#" index="outerRow">
data in outerloop - <cfoutput>#getTestValues1.data[outerRow]#</cfoutput><br>
<cfquery name="getTestValues2" datasource="#ds#">
SELECT Data FROM yourTable
</cfquery>
<cfloop from="1" to="#getTestValues2.recordCount#" index="innerRow">
data in innerloop - <cfoutput>#getTestValues2.data[innerRow]#</cfoutput>
</cfloop>
<br>
</cfloop>

[Dan Bracuk]

But don't put the second query inside the first loop.

[mxstu]

As I said "It is a good idea to avoid nesting cfquery's inside a loop, for
performance reasons." I would assume this is just a development test since a
nested query with no WHERE clause doesn't usually make a lot of sense.

[Dan Bracuk]

But what I meant was, if you are going to nest query outputs, don't put the 2nd cfquery tag inside the first loop, or you will run it more than once.

[mxstu]

True, it would, but I am assuming that the test code structure is related to
what the OP is really trying to do, which I am guessing involves nesting
queries with some kind of WHERE clause. In which case they do want the query
to execute more than once. This is why my example includes the cfquery within
the loop, even though it really is not necessary in this case.
That being said, I'll repeat my earlier disclaimer "nesting cfqueries within a
loop should be avoided".

Of course this is all just guessing until the OP provides more information ;-)