7 Replies Latest reply: Jul 31, 2009 3:17 AM by BKBK RSS

    Strange Recursive Query of Query error.

    ilssac Community Member

      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.