Empty Category in Category Tree 1

[webmigit]

I'm sure most of us are familiar with the infinite-depth category system, where all categories are in the same table and each has a parent id referring to 0 (top level category) or the the id of another category.

Is there a fast way to return these categories, but only if the category, or the child category (or the grand child, or so on...) has a product in it?

I know that you can essentially do (sorry if this is a little rough, I'm more SQL-Server, so I tend to lean towards it when I'm giving examples).

Select * from cats
 where catParentID = #url.catParentID#
   and catID in (select catID from products)

But that only works for checking categories that are immediate children of the current category.

Thanks for all your help

 

[r937]

is there a fast way to return what, again?

r937.com | rudy.ca

 

[TonyGroves]

I remember this type of problem being raised a while back, and somebody posred
a link to this article at the MySQL site:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

; though
I've no idea if it's useful in this situation or not.

There are two ways I can imagine it being done:
(1) Write a query with several nested sub-queries or left joins, as many as
the maximum number of generations in the table.
(2) Have your program issue a simple query repeatedly until all the generations
have been processed.

 

[webmigit]

Rudy,

I'm trying to get it to return categories who contain products or contain categories who contain products or who contain categories who contain categories... and so on.

Tony

I figured the answer would be just about what you said, about needing numerous joins to match the depth.

I'll look over the article you suggested, but I may have think I'll go with what I feared I was going to anyway... I'll write a script that climbs through all the categories checking them one by one to see if... somewhere in their hierarchy, they contain products.. and if so, they stay visible, otherwise they're marked invisible. I can set this page to run on say... the first visit to the site each day.. or make it an admin function.

Thanks for weighing in. I find the mysql site tough to navigate so I don't check there often.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

Revisions to my first idea.. As a note to anyone who may come along this post with the same need.

The script should actually total number of products for each category that are inside it or inside categories inside it. This is good for two reasons:

You'd want to create another field for those marked visible or invisible by the script because of whether or not they contain products. If you already have a visible field, the trouble that you have is that later, when a product is added, you must discern whether a person *wanted* the category in which it resides to be invisible or whether your script did it.

Since the additional field is needed, its best to store the count inside it because then you can display it in your application..

And where your query would say..

#CatName# (#productsIn# products)

Adding another useful feature to your system.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.