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

Question

我正在用InnoDB引擎为一个购物应用程序构建一个MySQL关系数据库，作为一个练习(我是一个新手，对于初学者的问题，我事先很抱歉(。

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

• 类别

1. category_id PK
2. 名称
3. parent_id

• 产品

1. id PK
2. 名称
3. desc
4. 价格
5. category_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 的类别(和子类别(中选择所有产品

这是Fiddle链接