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" <[email protected]> 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 > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194673 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
