Select products where the category belongs to any category in the hierarchy

Question

I have a products table that contains a FK for a category, the Categories table is created in a way that each category can have a parent category, example:

Computers
    Processors
        Intel
            Pentium
            Core 2 Duo
        AMD
            Athlon

I need to make a select query that if the selected category is Processors, it will return products that is in Intel, Pentium, Core 2 Duo, Amd, etc...

I thought about creating some sort of "cache" that will store all the categories in the hierarchy for every category in the db and include the "IN" in the where clause. Is this the best solution?

Answer 1

The best solution for this is at the database design stage. Your categories table needs to be a Nested Set. The article Managing Hierarchical Data in MySQL is not that MySQL specific (despite the title), and gives a great overview of the different methods of storing a hierarchy in a database table.

Executive Summary:

Nested Sets

• Selects are easy for any depth
• Inserts and deletes are hard

Standard parent_id based hierarchy

• Selects are based on inner joins (so get hairy fast)
• Inserts and deletes are easy

So based on your example, if your hierarchy table was a nested set your query would look something like this:

SELECT * FROM products 
   INNER JOIN categories ON categories.id = products.category_id 
WHERE categories.lft > 2 and categories.rgt < 11

the 2 and 11 are the left and right respectively of the Processors record.