选择类别属于层次结构中任何类别的产品 [英] Select products where the category belongs to any category in the hierarchy

问题描述

我有一个产品表，其中包含一个类别的FK，创建类别表的方式是每个类别都可以有一个父类别，例如:

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

我需要进行选择查询，如果选择的类别是处理器"，它将返回英特尔，奔腾，Core 2 Duo，AMD等产品.

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...

我考虑过创建某种缓存"，它将为数据库中的每个类别存储层次结构中的所有类别，并在where子句中包含"IN".这是最好的解决方案吗?

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?

推荐答案

最好的解决方案是在数据库设计阶段.您的类别表必须为嵌套集.文章在MySQL中管理分层数据不是特定于MySQL的( (尽管有标题)，并概述了在数据库表中存储层次结构的不同方法.

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.

• 任何深度的选择都很容易
• 插入和删除都很困难

• 选择是基于内部联接的(因此可以快速获得毛发)
• 插入和删除都很容易

因此，根据您的示例，如果您的层次结构表是一个嵌套集，则查询将如下所示:

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

2和11分别是Processors记录的左侧和右侧.

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

这篇关于选择类别属于层次结构中任何类别的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！