How to recursively join table onto all records of another table?

February 1, 2023

Suppose the following setup,

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);

I’d like to query for all my_schema.product records, with breadcrumbs for their category.

For instance, I’m trying to get all my_schema.product records, here is the result that I am expecting from the query:

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

If I want to get my_schema.product record with id = 20, here is the result I’d expect:

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

With the help of some answers on stackoverflow, I’ve managed to get a recursive query for my_schema.category:

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

But I don’t know how to join this result onto my_schema.product, as above.

Here is my fiddle

>Solution :

You can do it as follows by joining the result to the product table using inner join :

    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 p.id, t.breadcrumbs
    FROM (
        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
    ) as t
    inner join my_schema.product as p on p.category_id = CAST (t.selectedCat AS INTEGER)
    order by p.id