Strange Recursive Query of Query error.

[ilssac]

It seems to involve a field in my main query that can contain null or integer values.

AreaList recordset

SELECT          a.ID AS a_id, a.NAME AS a_name, a.PARENTID, a.geo_order AS a_order,
          l.id AS l_id, l.name AS l_name, l.geo_order AS l_order,
          j.areaId
                    
FROM          Area a LEFT OUTER JOIN
          Location_Area_Join j ON (a.id = j.areaId) LEFT OUTER JOIN
          Locations l ON (j.locationid = l.id)
                    
ORDER BY     a.ParentID, a.ID, l.name

The a.geo_order and l.geo_order fields can contain either nulls or integers.

I then run this recursive function to output that query in a parent-child tree relationship.

area_recurse function

<cffunction name="area_recurse" returntype="string" output="false">
     <cfargument name="parentid" required="true" type="numeric">

     <cfset var returnVar = "">
     <cfset var oneLevel = "">
     
     <cfquery dbtype="query" name="oneLevel">
          SELECT *
          FROM AreaList
          WHERE parentid = #arguments.parentid#
          ORDER BY a_order, a_name, l_order, l_name
     </cfquery>
     
     <cfsavecontent variable="returnVar">
     <cfdump var="#oneLevel#">
     <ul>
          <cfoutput query="oneLevel" group="areaid">
          <li><input type="text" size="1" maxlength="3" name="a-#a_id#" value="#a_order#"/>  <strong>Area: #a_id# (#parentid#) #a_name#</strong> #killme#
          <cfif len(trim(areaid)) GT 0>
          <ul class="locations">
          <cfoutput>
          <li><input type="text" size="1" maxlength="3" name="l-#l_id#" value="#l_order#"/>  Location: #l_id# (#areaid#) #l_name#</li>
          </cfoutput>
          </ul>
          </cfif>
          #area_recurse(a_id)#
          </li>
          </cfoutput>
     </ul>
     </cfsavecontent>
     
     <cfreturn returnVar>
</cffunction>

If I run this code as is, it will only display the first record of each query-of-query record set.  But a dump of the record set in each iteration of the function shows that the query of query generated the complete and correct record set.  But the <cfoutput query...> loop does not loop over them.  This can be seen here: http://www.sierraoutdoorrecreation.com/Geo-Order.cfm.  If I modify the base query to provide a default integer for the geo-order fields or remove the geo-order fields from the query-of-query SQL statement, it all works as expected.  The loops will loop over all the complete record sets for every iteration and display the entire tree.  Does this make sense to anybody?  Is this expected behavior somehow?  How does the <cfdump...> logic display the entire record sets, but my <cfoutput query...> loops do not?

TIA

Ian

P.S. I am not really looking for replacements for my tree building logic, I know how to do that.  I am just wanting to understand this particular strange behavior.

[Dan_Bracuk]

Is this combination relevent?

ORDER BY a_order, a_name, l_order, l_name
<cfoutput query="oneLevel" group="areaid">

[mack_]

Maybe cfloop and cfoutput have some kind of internal counter/query and

this counter gets somehow reset when the QoQ retuns no more rows (for

example when the function is called for level 2 or 3) ? Just a guess

tough...

Mack

[ilssac]

I don't think this is a factor.  Since the logic works fine if I elminate the field with the nulls or the nulls from the field in the order by clause.  If I do either of those the function will correctly recurse through the data building the tree, even when it reaches nodes that return empty record sets.

[Guest]

I'm attempting to do a recursive query of a query, but i can't get the cfc to see my original query that is in my cfm file.  How can i have the component see the query?

Thanks

[Dan_Bracuk]

send it as an argument

[Guest]

i thought that might be the answer, so i tried it, and it did work that way.  So there are no known issues with passing a large query over and over?

[BKBK]

I think the value of cfoutput's group attribute should be a column from the query's order-by clause.