Suppose the following,

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,
        'Around the house',
        '{2}'
    ),
    (
        4,
        'Personal',
        '{3}'
    );

I'm trying to create breadcrumbs on the frontend. For instance, if Electrician is the selected category, I'd like to render something like Personal > Around the house > Electrician.

Ideally, I'd like the query to return something like,

{
    "id": 2,
    "breadcrumbs": "Personal.Around the house.Electrician"
}

This way, I could just do breadcrumbs.split('.').map(. . .).

Another result that would work is something like,

{
    "id": 2,
    "category_name": "Electrician"
    "breadcrumbs": [
        {
            "id": 4,
            "category_name": "Personal"
        },
        {
            "id": 3,
            "category_name": "Around the house"
        },
    ]
}

Or even,

{
    "breadcrumbs": [
        {
            "id": 4,
            "category_name": "Personal"
        },
        {
            "id": 3,
            "category_name": "Around the house"
        },
        {
            "id": 2,
            "category_name": "Electrician"
        }
    ]
}

How can I attain such a result?

CodePudding user response：

You can do it using WITH RECURSIVE as follows :

to get any breadcrumbs just put its id on the first select where id = 2

WITH RECURSIVE cte(n, id, selectedCat) AS
(
  SELECT 1, id, id::text
  from category 
  where id = 2
  UNION ALL
  SELECT n 1, e.id, ep.selectedCat
  FROM cte AS ep JOIN 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 category c on c.id = cte.id
  order by n desc
) AS s
GROUP BY selectedCat

demo here