选择所有产品并通过子类别（未知级别）加入主要类别 [英] select all products and join main category through sub-categories (unknown level)

问题描述

我有2张桌子

类别

id - name - parent
1 - Category A - 0
2 - Category B - 0
3 - Category C - 0
4 - Category D - 0
5 - Subcategory Of 1 - 1
6 - Subcategory Of 5 - 5
7 - Subcategory Of 5 - 5

产品

id - name - category - description
1  - Name - 5 - Description

如何选择所有产品并通过子类别加入主要类别？产品类别只能有1或2或3或4个级别（未知级别）。

How to select all products and join main category through sub-categories? Product categories can has only 1 or 2 or 3 or 4 levels (Unknown level).

我在类别表中使用了 WITH RECURSIVE，但找不到找到方法将产品表与1次查询相结合

I use "WITH RECURSIVE" in categories table but can't find the way to combine product table with 1 time query

WITH RECURSIVE category_child AS 
(
    SELECT * FROM categories as c WHERE c.id = 5
    UNION ALL
    SELECT c2.* FROM categories AS c2
    JOIN category_child as c3 ON c3.parent_id = c2.id
)

执行此操作的最佳方法是什么？

What's the best way to do this ?

预期结果

id - name - category - description - root - sub category id 1 - sub category id 2 - sub category id 3

OR

id - name - category - description - root
id - name - category - description - sub category id 1
id - name - category - description - sub category id 2
id - name - category - description - sub category id 3 

推荐答案

要获得类别的完整路径，您不能以 c.id = 5 开始非递归部分，而必须从使用的root，其中parent_id为null （您应该不标识具有不存在的类别ID的根节点，这会阻止为该节点创建适当的外键

As you want the complete path to a category, you can't start your non-recursive part with c.id = 5 you have to start at the root using where parent_id is null (you should not identify the root nodes with a non-existing category ID, that prevents creating a proper foreign key for the parent_id column).

然后在递归部分中，您可以汇总根目录类别的完整路径：

In the recursive part you can then aggregate the full path to the root category:

with recursive tree as 
(
  select *, id as root_category, concat('/', name) as category_path
  from category
  where parent_id is null
  union all
  select c.*, p.root_category, concat(p.category_path, '/', c.name)
  from category c
    join tree p on c.parent_id = p.id
)
select p.id as product_id,
       p.name as product_name,
       t.root_category,
       t.category_path
from tree t
  join product p on p.category = t.id

使用以下示例数据：

create table category (id integer, name text, parent_id integer);
create table product (id integer, name text, category integer, description text);

insert into category
values
(1, 'Category A', null),
(2, 'Category B', null),
(3, 'Category C', null),
(4, 'Category D', null),
(5, 'Subcategory Of 1', 1),
(6, 'Subcategory Of 5', 5),
(7, 'Subcategory Of 5', 5),
(8, 'Subcategory of D', 4)
;

insert into product
values
(1, 'Product One', 5, 'Our first product'),
(2, 'Product Two', 8, 'The even better one');

返回值：

product_id | product_name | root_category | category_path               
-----------+--------------+---------------+-----------------------------
         1 | Product One  |             1 | /Category A/Subcategory Of 1
         2 | Product Two  |             4 | /Category D/Subcategory of D

这篇关于选择所有产品并通过子类别（未知级别）加入主要类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！