Re: category-unlimited subcategory formatted dropdowns
First, all those queries are going to be really slow. The code as-is
will be making n+m+1 queries to generate the dropdown, where n is the
number of individual elements, and m is the number of elements with
children. The "+1" is for the initial query for items with no parent.
And yes, you can count the same element in both n and m, so worst
case scenario would be 2n queries.
However, depending on what you're doing with the result, that might
not matter. For example, if you're caching it. Or, if it's a low-use
item, then it might not be worth the time to optimize. One alternate
route would be to use a nested set model.
But that's not your question. To solve the dash insertion problem,
make another optional attribute named 'depth' (or 'level') which is
omitted from the initial call (just like parentItemID) and defaults to
zero. Then add #repeatString("-", attributes.depth)# before the
category name within the option. Finally, pass a 'depth' attribute on
the recursive calls, incrementing by one. So the call with look like
this:
cheers,
barneyb
On Tue, 16 Nov 2004 23:31:31 -0500, Emmet McGovern
<[EMAIL PROTECTED]> wrote:
> I have a table that contains categories and unlimited subcategories
> referencing and I need to create a formatted drop down list. I've been
> messin around with it for a while and now with no luck. I need the list to
> go like so...
>
> Category Name
> --Child of category
> --Child of category
> Child of sub category
> --Child of sub sub category
> --Child of category
>
> I can get the data out in the proper order, I just cant seem to get the --
> in there.
>
> I'm using this code as a custom tag which calls itself back again for the
> recursion.
>
> Thanks for the help
> -emmet
>
>
>
>
>
>
>
>
>
>
>
>
> SELECT *
> FROM category
> WHERE ParentID = #Variables.ParentItemID#
> ORDER BYname
>
>
>
>
>
> 0>class="lightcell">#GetCurrentCat.name#
>
>
>
>
> SELECT *
> FROM category
> WHERE parentID = #GetCurrentCat.catID#
>
>
>
>
> ParentItemID="#GetCurrentCat.catID#"
> dsn="#attributes.dsn#">
>
>
>
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/blog/
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184523
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
RE: category-unlimited subcategory formatted dropdowns
I'd call you brilliant but you made fun of my chunk of code ;) I cant
believe I overlooked repeatstring()! Thanks you thank you thank you.
Unfortunately I'm not the greatest when it comes to complex sql so I never
looked at nested set to start with. Care to enlighten?
My late night is done! Hoorah!
-e
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 17, 2004 1:01 AM
To: CF-Talk
Subject: Re: category-unlimited subcategory formatted dropdowns
First, all those queries are going to be really slow. The code as-is
will be making n+m+1 queries to generate the dropdown, where n is the
number of individual elements, and m is the number of elements with
children. The "+1" is for the initial query for items with no parent.
And yes, you can count the same element in both n and m, so worst
case scenario would be 2n queries.
However, depending on what you're doing with the result, that might
not matter. For example, if you're caching it. Or, if it's a low-use
item, then it might not be worth the time to optimize. One alternate
route would be to use a nested set model.
But that's not your question. To solve the dash insertion problem,
make another optional attribute named 'depth' (or 'level') which is
omitted from the initial call (just like parentItemID) and defaults to
zero. Then add #repeatString("-", attributes.depth)# before the
category name within the option. Finally, pass a 'depth' attribute on
the recursive calls, incrementing by one. So the call with look like
this:
cheers,
barneyb
On Tue, 16 Nov 2004 23:31:31 -0500, Emmet McGovern
<[EMAIL PROTECTED]> wrote:
> I have a table that contains categories and unlimited subcategories
> referencing and I need to create a formatted drop down list. I've been
> messin around with it for a while and now with no luck. I need the list
to
> go like so...
>
> Category Name
> --Child of category
> --Child of category
> Child of sub category
> --Child of sub sub category
> --Child of category
>
> I can get the data out in the proper order, I just cant seem to get the --
> in there.
>
> I'm using this code as a custom tag which calls itself back again for the
> recursion.
>
> Thanks for the help
> -emmet
>
>
>
>
>
>
>
>
>
>
>
>
> SELECT *
> FROM category
> WHERE ParentID = #Variables.ParentItemID#
> ORDER BYname
>
>
>
>
>
> 0>class="lightcell">#GetCurrentCat.name#
>
>
>
>
> SELECT *
> FROM category
> WHERE parentID = #GetCurrentCat.catID#
>
>
>
>
> ParentItemID="#GetCurrentCat.catID#"
> dsn="#attributes.dsn#">
>
>
>
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/blog/
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184525
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
Re: category-unlimited subcategory formatted dropdowns
I wasn't making fun of your code. I'll freely admit to having writen code very similar to what you listed, though not recently. ;) Rather I was pointing out deficiencies and explaining them, and my blunt, straightforward mannerisms were once again warped by email and misconstrued. So take no offence to my comments. The nested set model is a means of storing hierarchial data in a relational database. As the names suggest, the two types of data are different, and consequently there are problems storing one type of data in a database designed for the other type. As an example of the other way around, XML is very good at storing hierarchies, but not particularly good at storing relationships. Here's a couple links that discuss the problem. http://www.dbazine.com/tropashko4.shtml http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=30247 I've found that data stored in a tree is often "mangement-ish" data (folders for organizing documents, categories for organizing products, etc.), rather than the core data (the documents or products). Thus it's particularly well suited to caching, as it probably doesn't change much. If that's the case, the simplicity of the parentID-itemID mechanism is very attractive, since the performance issues can be avoided through caching. Do your expensive recursive operation (though hopefully with a few less queries) once, but rather than generate a list of OPTIONs, generate a recordset that contains the items in the tree order, with the level as a field (along with whatever other metadata you want). Cache that result somewhere, and update it when the categories change. Then whenever you need to use the tree, you can just loop over that recordset and generate what you want. cheers, barneyb On Wed, 17 Nov 2004 01:16:53 -0500, Emmet McGovern <[EMAIL PROTECTED]> wrote: > I'd call you brilliant but you made fun of my chunk of code ;) I cant > believe I overlooked repeatstring()! Thanks you thank you thank you. > > Unfortunately I'm not the greatest when it comes to complex sql so I never > looked at nested set to start with. Care to enlighten? > > My late night is done! Hoorah! > > -e > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184527 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
RE: category-unlimited subcategory formatted dropdowns
"I was pointing out deficiencies and explaining them, and my blunt, straightforward mannerisms were once again warped by email and misconstrued. So take no offence to my comments." Actually, you can apply this statement to me. I was joking and being sarcastically appreciative of the fix. In this case, it'll work out fine. The categories and subs wont change often and will be cached. Thanks for the links though. The 2nd one was a perfect example. I've been cruising and bookmarked your blog since the original reply you sent. -e ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184528 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
RE: category-unlimited subcategory formatted dropdowns
You could also look at this custom tag that does a great job ..
http://www.niederhausen.net/piet/dev/customtags/traversetree.html
Pat
-Original Message-
From: Emmet McGovern [mailto:[EMAIL PROTECTED]
Sent: November 17, 2004 01:17
To: CF-Talk
Subject: RE: category-unlimited subcategory formatted dropdowns
I'd call you brilliant but you made fun of my chunk of code ;) I cant
believe I overlooked repeatstring()! Thanks you thank you thank you.
Unfortunately I'm not the greatest when it comes to complex sql so I never
looked at nested set to start with. Care to enlighten?
My late night is done! Hoorah!
-e
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 17, 2004 1:01 AM
To: CF-Talk
Subject: Re: category-unlimited subcategory formatted dropdowns
First, all those queries are going to be really slow. The code as-is will
be making n+m+1 queries to generate the dropdown, where n is the number of
individual elements, and m is the number of elements with children. The
"+1" is for the initial query for items with no parent.
And yes, you can count the same element in both n and m, so worst case
scenario would be 2n queries.
However, depending on what you're doing with the result, that might not
matter. For example, if you're caching it. Or, if it's a low-use item,
then it might not be worth the time to optimize. One alternate route would
be to use a nested set model.
But that's not your question. To solve the dash insertion problem, make
another optional attribute named 'depth' (or 'level') which is omitted from
the initial call (just like parentItemID) and defaults to zero. Then add
#repeatString("-", attributes.depth)# before the category name within the
option. Finally, pass a 'depth' attribute on the recursive calls,
incrementing by one. So the call with look like
this:
cheers,
barneyb
On Tue, 16 Nov 2004 23:31:31 -0500, Emmet McGovern
<[EMAIL PROTECTED]> wrote:
> I have a table that contains categories and unlimited subcategories
> referencing and I need to create a formatted drop down list. I've
> been messin around with it for a while and now with no luck. I need
> the list
to
> go like so...
>
> Category Name
> --Child of category
> --Child of category
> Child of sub category
> --Child of sub sub category
> --Child of category
>
> I can get the data out in the proper order, I just cant seem to get
> the -- in there.
>
> I'm using this code as a custom tag which calls itself back again for
> the recursion.
>
> Thanks for the help
> -emmet
>
> name="parentID"
>
>
>
>
>
> IsDefined("Attributes.ParentItemID")>
>
>
>
>
> SELECT *
> FROM category
> WHERE ParentID = #Variables.ParentItemID#
> ORDER BYname
>
>
>
>
>
> 0>class="lightcell">#GetCurrentCat.name#
>
>
>
>
> SELECT *
> FROM category
> WHERE parentID = #GetCurrentCat.catID#
>
>
>
>
> ParentItemID="#GetCurrentCat.catID#"
> dsn="#attributes.dsn#">
>
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/blog/
~|
Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net
http://www.cfhosting.net
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184550
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
