> S. Isaac Dealey wrote:
>> Before you give up... There's a couple of things you can
>> do for the
>> recursion in the db... One is to create a cross-reference
>> table which shows
>> the parent-child relationship between all records in the
>> table...
>> Alternatively, you could try this:
>>
>> <cfset rs = QueryNew("ID")>
>> <cfset temp = QueryAddRow(rs)>
>> <cfset rs.id[1] = "0">
>> <cfset rs2 = QueryNew("ID")>
>>
>> <cfloop condition="rs.recordcount">
>> <cfquery name="rs" datasource="...">
>> SELECT * FROM mytable
>> WHERE parentid IN (#valuelist(rs.id)#)
>> </cfquery><cfquery name="rs3" dbtype="query">
>> SELECT * FROM rs UNION SELECT * FROM rs2
>> </cfquery><cfset rs2 = rs3>
>> </cfloop>
>>
>> This or something like it ought to give you a single
>> query containing all of
>> the children of the parent... It will be much slower than
>> a cross-reference
>> table in the db, however, it should be similar in
>> performance to the current
>> solution... Once you have this query, however, you can
>> then use the
>> recursive UDF in CF 5 and either pass the query to the
>> UDF along with the
>> current parent id for the tree, or you can place the
>> query in the request
>> scope and reference it from there...
>
> never do to much work in cf when your database can do it
> faster
Well, that's why I recommended that a cross-reference table would be
faster... :) All my recursion is done using the NTM model described by that
guy who wrote the book "SQL for Smarties" ... don't remember his name --
never actually read the book -- discovered the method was documented after I
had figured out how to do it on my own... But if the page in question
doesn't see huge amounts of traffic, or if the tree structure isn't
tremendously deep ( width isn't much of a problem ) then it's not wholly
unreasonable in terms of performance to do it in cf as above...
Isaac
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
