Recursion, and the query

[csteinhilber]

I'm having a problem recursing through a hierarchy with a query.

I have a custom tag that calls itself, and if a given state is true, I want that current level and all child levels to loop through a resultset:

recursing_monster.cfm-

    :
   <CFIF hasChildren is TRUE>
      <CFIF datasource is TRUE>
         <CFLOOP query=&quot;queryname&quot; ...>
            <CF_RECURSING_MONSTER ...>
         </CFLOOP>
      <CFELSE>
         <CF_RECURSING_MONSTER ...>
      </CFIF>
   </CFIF>

   Process current hierarchy level
   <CFOUTPUT>#queryname.fieldname#</CFOUTPUT>, etc.

Problem is, when I loop on the query and recursively call CF_RECURSING_MONSTER, I can get the caller.queryname in the child, but the current row is always 1.

Anybody see any way to actually loop through the query? I've been beating my head against the wall on this for 4 days, and I've lost all ability to look at it subjectively.

Thanks in advance!

-Carl

 

[flowcontrol]

Is your queryname the same at each level of recursion? If the query name is the same at each level, then I think you are wiping out the higher level query's recordset each time you call recursing_monster.cfm.

Try using a dynamic queryname. You can do this by tracking a "depth" variable that is passed into RECURSING_MONSTER. This variable contains the current depth (1,2,3, ...). It gets incremented by RECURSING_MONSTER before it does anything else. Then, you make your query name "monster#attributes.depth#". You initialize it to 0 or 1 (depending on how your recursion is set up) before you call recursing_monster.cfm.

recursing_monster.cfm-
<cfset variables.depth = attributes.depth + 1>
:
<CFIF hasChildren is TRUE>
<CFIF datasource is TRUE>
<CFLOOP query=&quot;queryname&quot; ...>
<CF_RECURSING_MONSTER depth=&quot;#variables.depth#&quot; ...>
</CFLOOP>
<CFELSE>
<CF_RECURSING_MONSTER depth=&quot;#variables.depth#&quot; ...>
</CFIF>
</CFIF>

Process current hierarchy level
<CFOUTPUT>#queryname.fieldname#</CFOUTPUT>, etc.

 

[csteinhilber]

Thanks Flowcontrol.

As far as I've been able to determine (and according to the docs), each recursion of the custom tag will be it's own variables scope (that's why I'm using a custom tag in the first place, rather than an include... which would inherit the scope).

So I don't think I'm wiping out the higher level's query... but the child query isn't inheriting the higher level's currentRow, either... which I think is where the problem lays.

I can't change the name of the query, because the processing at each level uses

#queryname.fieldname#

... so the query name needs to be consistant during the entire recursion (again, the very reason I used a custom tag rather than an include).

I nearly solved the issue, though... by maintaining and passing a variable, much like depth, called &quot;rowNum&quot; and when I pull the query from parent to child using a query-on-query, I use:

  <cfquery name=&quot;#whichQuery#&quot; dbtype=&quot;query&quot;>
	SELECT *
  	FROM caller.#whichQuery#
	<CFIF Len(Trim(Attributes.rowNum)) GT 0>WHERE ROWNUM = '#Attributes.RowNum#'</CFIF>
   </cfquery>

at least... that's what I would use for Oracle. But query-on-query's don't like &quot;WHERE ROWNUM = ...&quot;

Anybody know ColdFusion's syntax for ROWNUM = for dbtype=&quot;query&quot;??

-Carl

 

[r937]

CurrentRow function

http://livedocs.macromedia.com/cf50docs/Developing_ColdFusion_Applications/queryDB10.jsp#1096548

rudeboy

 

[csteinhilber]

That's what I thought it'd be, too, Rudy...

but

<cfquery name=&quot;#whichQuery#&quot; dbtype=&quot;query&quot;>
  SELECT *
  FROM caller.#whichQuery#
  <CFIF Len(Trim(Attributes.rowNum)) GT 0>WHERE CurrentRow = '#Attributes.RowNum#'</CFIF>
</cfquery>

bombs with

Query Manipulation Error Code = 0

Can't find symbol: CurrentRow


:-(
-Carl

 

[r937]

#CurrentRow#

 

[csteinhilber]

Doh! AND I just realized it'd probably be an integer and not a string




-Carl

 

[csteinhilber]

Hmmmm... still doesn't work, Rudy. :-(

I tried:

<cfquery name=&quot;#whichQuery#&quot; dbtype=&quot;query&quot;>
   SELECT *
   FROM structDataFile.#whichQuery#
   <CFIF Len(Trim(Attributes.rowNum)) GT 0>WHERE #CurrentRow# = #Attributes.RowNum#</CFIF>
</cfquery>

and even tried:

<cfquery name=&quot;#whichQuery#&quot; dbtype=&quot;query&quot;>
   SELECT *
   FROM structDataFile.#whichQuery# a
   <CFIF Len(Trim(Attributes.rowNum)) GT 0>WHERE a.#CurrentRow# = #Attributes.RowNum#</CFIF>
</cfquery>

and it still blows up with:
Error Diagnostic Information

An error occurred while evaluating the expression:
#CurrentRow#

Error resolving parameter CURRENTROW


If you notice, things changed just a bit, in that all my resultsets are now stored in a structure, with the key equal to the name of the query that produced them. This proved to be far more reliable and efficient than using caller. I do not think this is the cause of the present problem, though, because it behaves just like a query in all other instances.

Indeed, I just tried the following test:

<CFSCRIPT>
	qryTemp = QueryNew(&quot;event,time&quot;);   
        QueryAddRow(qryTemp, 4);

	QuerySetCell(qryTemp,&quot;event&quot;,&quot;Breakfast&quot;,1);
	QuerySetCell(qryTemp,&quot;time&quot;,&quot;9:00 AM&quot;,1);
	
	QuerySetCell(qryTemp,&quot;event&quot;,&quot;Lunch&quot;,2);
	QuerySetCell(qryTemp,&quot;time&quot;,&quot;11:30 AM&quot;,2);

	QuerySetCell(qryTemp,&quot;event&quot;,&quot;Dinner&quot;,3);
	QuerySetCell(qryTemp,&quot;time&quot;,&quot;6:00 PM&quot;,3);

	QuerySetCell(qryTemp,&quot;event&quot;,&quot;Snack&quot;,4);
	QuerySetCell(qryTemp,&quot;time&quot;,&quot;Midnight&quot;,4);
</CFSCRIPT>

<CFDUMP var=&quot;#qryTemp#&quot;>

<CFQUERY name=&quot;newquery&quot; dbtype=&quot;query&quot;>
   SELECT *
   FROM qryTemp
   WHERE #CurrentRow# = 2
</CFQUERY >

and I still get the Error resolving parameter CURRENTROW

What am I doing wrong?


-Carl

 

[r937]

carl, first of all, i don't do my own structures

stuff comes from the database, i leave it in the query structure that it came in on

now, if i want to run another query, that would be a query-of-query, that is based on CurrentRow of the first query, then i had better qualify it, no?

you have

<CFQUERY name=&quot;newquery&quot; dbtype=&quot;query&quot;>
SELECT *
FROM qryTemp
WHERE #CurrentRow# = 2
</CFQUERY >

and i'm wondering why you're even querying it, if all you want to do is pull out a certain row

after all, qryTemp is supposed to be a structure, my good man, although i personally don't do stuff like this

i like my stuff simpler

anyhow, i hope this points you in the right direction


rudy

 

[csteinhilber]

Well... because of everything that came before it in this thread.

I have a custom tag that recurses itself. In order to effectively pass the queries from parent to child, I stuff all of them into a structure and pass the structure (this is also how the data initially comes out of a WDDX file) and I pull the structure apart by executing a query-on-query on the value of the structure element that has the key named after the query I'm after (because Duplicate wasn't working... thread232-498428). With me? (believe me... I like my stuff simpler, too... but simpler isn't cuttin' it, so here I sit)

At any rate, when the tag recurses, under certain circumstances it is looping over a resultset... in which case all it's children must loop over the same row as the parent. Thus, I pass the child a rowNum variable that contains the value of the current row that the parent is on. IF the tag recurses and finds that a rowNum was passed, it needs to retrieve just that row out of the query. Thus:

<cfquery name=&quot;#whichQuery#&quot; dbtype=&quot;query&quot;>
   SELECT *
   FROM structDataFile.#whichQuery#
   <CFIF Len(Trim(Attributes.rowNum)) GT 0>WHERE #CurrentRow# = #Attributes.RowNum#</CFIF>
</cfquery>

ie - if rowNum is passed, just retrieve that row.

But, as we discussed, #CurrentRow# doesn't work. So I created a small test (see above) to see if it was the query-in-a-structure that was giving it fits. Turns out it wasn't. #CurrentRow# just plain doesn't work.

Which brings me back to the question: why?

-Carl

 

[r937]

perhaps CurrentRow is only valid if you are actually looping over the result set with CFOUTPUT

but since you are passing a value in, #Attributes.RowNum#, why don't you just yank that occurrence out of the structure? i mean, do you need to use query-of-query if all you want is one of its rows?

so, just extract blahblah[#Attributes.RowNum#]

except i don't know what the blahblah is, that's the part of structures i have little experience with


rudy