如何将递归生成的分类面包屑关联至所有产品记录？

数据库结构 #

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.category (
    id serial PRIMARY KEY,
    category_name VARCHAR (255) NOT NULL,
    subcategories BIGINT[] DEFAULT ARRAY[]::BIGINT[]
);
INSERT INTO my_schema.category VALUES
    (1, 'Pickup/dropoff', '{}'),
    (2, 'Electrician', '{}'),
    (3, 'Plumber', '{}'),
    (4, 'Around the house', '{2,3}'),
    (5, 'Personal', '{4}');

CREATE TABLE IF NOT EXISTS my_schema.product (
    id serial PRIMARY KEY,
    category_id BIGINT REFERENCES my_schema.category (id) NOT NULL
);

INSERT INTO my_schema.product VALUES
    (10, 2),
    (20, 3),
    (30, 4);

需求 #

查询所有my_schema.product记录，并附带其分类的面包屑路径。示例结果如下：

[
    {
        "id": 10,
        "breadcrumbs": "Personal,Around the house,Electrician"
    },
    {
        "id": 20,
        "breadcrumbs": "Personal,Around the house,Plumber"
    },
    {
        "id": 30,
        "breadcrumbs": "Around the house"
    }
]

单独查询id=20的产品时，期望结果：

{
    "id": 20,
    "breadcrumbs": "Personal,Around the house,Plumber"
}

现有问题 #

已实现针对my_schema.category的递归查询，能生成各分类的面包屑，但不清楚如何将该结果与my_schema.product表关联以满足需求。现有递归查询代码：

WITH RECURSIVE cte(n, id, selectedCat) AS
(
    SELECT 1, id, id::text
    FROM my_schema.category
    UNION ALL
    SELECT n+1, e.id, ep.selectedCat
    FROM cte AS ep
    JOIN my_schema.category AS e
        ON ep.id = ANY (e.subcategories)
)
SELECT selectedCat, string_agg(category_name, ',') AS breadcrumbs
FROM (
    SELECT selectedCat, category_name
    FROM cte
    INNER JOIN my_schema.category c ON c.id = cte.id
    ORDER BY n DESC
) AS s
GROUP BY selectedCat

解决方案 #

将递归生成的分类面包屑结果作为子查询，与product表通过category_id关联即可。

查询所有产品及对应面包屑 #

WITH RECURSIVE cte(n, id, selectedCat) AS
(
    SELECT 1, id, id::text
    FROM my_schema.category
    UNION ALL
    SELECT n+1, e.id, ep.selectedCat
    FROM cte AS ep
    JOIN my_schema.category AS e
        ON ep.id = ANY (e.subcategories)
),
category_breadcrumbs AS (
    SELECT selectedCat::bigint AS category_id, string_agg(category_name, ',') AS breadcrumbs
    FROM (
        SELECT selectedCat, category_name, n
        FROM cte
        INNER JOIN my_schema.category c ON c.id = cte.id
        ORDER BY n DESC
    ) AS s
    GROUP BY selectedCat
)
SELECT p.id, cb.breadcrumbs
FROM my_schema.product p
JOIN category_breadcrumbs cb ON p.category_id = cb.category_id
ORDER BY p.id;

查询单个产品（以id=20为例） #

WITH RECURSIVE cte(n, id, selectedCat) AS
(
    SELECT 1, id, id::text
    FROM my_schema.category
    UNION ALL
    SELECT n+1, e.id, ep.selectedCat
    FROM cte AS ep
    JOIN my_schema.category AS e
        ON ep.id = ANY (e.subcategories)
),
category_breadcrumbs AS (
    SELECT selectedCat::bigint AS category_id, string_agg(category_name, ',') AS breadcrumbs
    FROM (
        SELECT selectedCat, category_name, n
        FROM cte
        INNER JOIN my_schema.category c ON c.id = cte.id
        ORDER BY n DESC
    ) AS s
    GROUP BY selectedCat
)
SELECT p.id, cb.breadcrumbs
FROM my_schema.product p
JOIN category_breadcrumbs cb ON p.category_id = cb.category_id
WHERE p.id = 20;

关键说明 #

1. 新增category_breadcrumbs子查询，将原递归结果的selectedCat转换为bigint类型，确保与product.category_id类型匹配；
2. 通过JOIN关联产品表和面包屑结果表，即可直接获取每个产品对应的完整分类路径。

内容的提问来源于stack exchange，提问作者Mike K