I was going to suggest Joe Celko, too... it is worth the ea! Dick
On Feb 14, 2005, at 8:12 PM, Rob Munn wrote: > Joe Celko has a great example of tree modelling using nested sets in > "SQL > for Smarties". In fact, he has a PHP class for doing just this released > under gpl. Check it out: > > http://phpclasses.mirrors.nyphp.org/browse/package/1374.html > > > > > ----- Original Message ----- > From: "Joe Rinehart" <[EMAIL PROTECTED]> > To: "CF-Talk" <cf-talk@houseoffusion.com> > Sent: Monday, February 14, 2005 6:26 PM > Subject: Re: Tree Traversal / Storage Algorithm > > >> It can also be done w/o recursion using what's known as "modified >> preorder tree traversal," which is a really neat extension of the >> adjancency list model. There's a good tutorial on it at >> http://www.sitepoint.com/article/hierarchical-data-database ....one >> day I'll blog it into CF. >> >> -joe >> >> >> >> On Tue, 15 Feb 2005 01:57:52 -0000, Paul Vernon >> <[EMAIL PROTECTED]> wrote: >>> Looks like you will be needing a recursive type of function to do > this... >>> >>> Funnily enough, I just wrote something for a manufacturers stock >>> control >>> system where they have an end product and the component tree used to >>> describe the item can be n levels deep and a items wide. I ended up > using >>> the function below to build a structure recursively by calling >>> itself so >>> that it traversed the entire structure one layer at a time. >>> >>> <cffunction name="retrieveItemComponents" access="public" >>> returnType="struct" output="true" >>> hint="Recursively lists all the child items for a > specific >>> parent item"> >>> >>> <cfargument name="ItemID" type="numeric" > required="true"> >>> >>> <cfquery name="q" datasource="#instance.dsn#"> >>> SELECT I.ItemName, S.ItemID AS ChildItemID >>> FROM Items I, SubItems S >>> WHERE I.ItemID = <cfqueryparam >>> value="#arguments.ItemID#" cfsqltype="CF_SQL_INTEGER"> >>> AND I.ItemID = S.ParentItemID >>> </cfquery> >>> >>> <cfif q.recordcount GT 0> >>> <cfset var ItemStruct = StructNew()> >>> >>> <cfset ItemStruct.siblings = ""> >>> <cfloop query="q"> >>> <cfset var currentItem = >>> listItems(-1,ChildItemID,-1)> >>> <cfset ItemStruct.ItemID = >>> ChildItemID> >>> <cfset ItemStruct.ItemName = >>> currentItem.ItemName> >>> <cfset ItemStruct.ItemCode = >>> currentItem.ItemCode> >>> <!--- here we go with the recursive > bit ---> >>> <cfset StructInsert(ItemStruct, >>> "ItemID#ChildItemID#", retrieveItemComponents(ChildItemID, >>> arguments.enabledOutput))> >>> <cfset ItemStruct.siblings = >>> ListAppend(ItemStruct.siblings, ChildItemID)> >>> </cfloop> >>> <cfreturn ItemStruct> >>> <cfelse> >>> <cfreturn var ItemStruct = StructNew()> >>> </cfif> >>> </cffunction> >>> >>> Essentially, we have to tables, Items is the one storing the items >>> and >>> SubItems has just two fields.. ItemID and ParentItemID. This allows >>> me > to >>> represent a tree structure of any width and depth... >>> >>> There are probably ways to do this in some forms of SQL but this >>> method > was >>> my only option. It's reasonably neat and produces a nice little > structure to >>> use... >>> >>> Paul >>> >>> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194674 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
