All,
Thank you again for all of your suggestions. As time allows, I will probably play
with all of them just to learn how.
It is wonderful to have such support.
Take care,
Robert
---------- Original Message ----------------------------------
From: S. Isaac Dealey <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Date: Fri, 13 Sep 2002 08:56:59 -0400
>> 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
