Recursive Function in Coldfusion

Question

I'm trying to create a recursive function in coldfusion and am coming across some issues.

Here is the logic I have:

<cffunction name="getEvents" access="private">
  <cfargument name="strtdate">
  <cfargument name="parentID" default=0>
  <cfqeury name="qry" datasource="db">
    select *
    from table
    where parentid = #parentid# and 
          starttime between #strtdate# and #DateAdd('d', 1, strtdate)#
  </cfquery>

  <cfset events = arraynew(1)>
  <cfloop query="qry">
    <cfset events[qry.currentrow] = structnew()>
    <cfset events[qry.currentrow].id = qry.id>
    <cfset subevents = getEvents(strtdate, qry.id)>
    <cfif arraylen(subevents)>
      <cfset events[qry.currentrow].subevents = subevents>
    </cfif>
  </cfloop>

  <cfreturn events>
</cffunction>

The problem is that once the function calls itself once it looses the original query in the loop. I now the events are three level deep but I don't want to have to right up the same coded over and over to handle all the events.

I would like to end up with an array of structs that contains all the events and subevents for a given day.

Answer 1

Make sure you var-scope your variables to keep them local to each invocation of the function.

In your function (at the top if in CF8 or earlier):

<cfset var qry = ''>
<cfset var events = ''>
<cfset var subevents = ''>

Answer 2

Try var scoping your query object. In fact you should use proper scoping in general. For example:

<cffunction name="getEvents" access="private">
  <cfargument name="strtdate">
  <cfargument name="parentID" default=0>

  <cfset var qry = "" />
  <cfset var events = "" />
  <!--- etc. --->


  <cfquery name="qry" datasource="db">
    select *
    from table
    where parentid = #parentid# and 
          starttime between #ARGUMENTS.strtdate# 
    and #DateAdd('d', 1, ARGUMENTS.strtdate)#
  </cfquery>

  ... etc.

Otherwise everything is going into the VARIABLES scope and overwriting others I suspect.

Hope that helps!

PS: You should also consider using <cfqueryparam /> in your queries.