如何使用邻接列表模型从MySQL分层类别树中获取数据？

Question

作为练习，我正在用InnoDB引擎为一个购物应用程序html" target="_blank">构建一个MySQL关系数据库(我是一个新手，对于初学者的问题，我提前表示歉意)。

对于产品类别，我决定使用邻接列表模型作为分层数据树。有两个关注表：

• 类别

1. category_id PK
2. 名称
3. parent_ id

• 产品

1. id主键
2. 名称
3. 描述
4. 价格
5. 类别_ id FK

我找到了一个来自Mike Hillyer的查询，用于检索完整的树：

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM categories AS t1
LEFT JOIN categories AS t2 ON t2.parent = t1.category_id
LEFT JOIN categories AS t3 ON t3.parent = t2.category_id
LEFT JOIN categories AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

但我不知道如何将其与产品表联系起来。

我使用引用这个问题的查询来获取特定类别(第19个元素)的孩子:

SELECT  category_id,
        name,
        parent_id 
FROM    (select * from categories
         order by parent_id, category_id) products_sorted,
        (select @pv := '19') initialisation
WHERE   find_in_set(parent_id, @pv)
AND     length(@pv := concat(@pv, ',', category_id))

我认为我应该使用JOIN从两个表中进行选择，但我没有得到正确的结果。

我想做的是，通过category_id获取产品，还要检索父类别。

此外，当用户单击根或任何级别的子类别时，我也想从子节点获取所有产品。

Answer 1

从给定类别(包括子类别)中获取所有产品的解决方案是

SELECT *
FROM    products
WHERE   category_id IN
(
    SELECT category_id
    FROM `categories`
    WHERE FIND_IN_SET(`category_id`, (
    SELECT GROUP_CONCAT(Level SEPARATOR ',') FROM (
      SELECT @Ids := (
          SELECT GROUP_CONCAT(`category_id` SEPARATOR ',')
          FROM `categories`
          WHERE FIND_IN_SET(`parent_id`, @Ids)
      ) Level
      FROM `categories`
      JOIN (SELECT @Ids := 1) temp1
   ) temp2
))
)

上面的查询从 ID 为 1 的类别（和子类别）中选择所有产品

这是小提琴链接