选择所有产品并通过子类别（未知级别）加入主类别

Question

我有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个级别(未知级别)。

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

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
)

最好的方法是什么？

预期结果

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

或

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 

Answer 1

当您想要类别的完整路径时，您不能以c.id=5开始您的非递归部分，您必须使用从根开始，其中parent_id为null（您不应该使用不存在的类别ID识别根节点，这会阻止为parent_id列创建正确的外键）。

在递归部分，您可以聚合根类别的完整路径：

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