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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:194646
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
