Retrieve all linked categories

Question

I have Categories stored in a single table.

Where there is no limit on number of childerns.

I want to fetch all the linked categories for the provided category id:

The reason for getting the hierarchy is that I need to update the path field for each category that is either newly created or updated. I need to maintain the path field

Table name: categories

id  parentId    name    path                    isLastLevel     
1   0           Cat 1   Cat 1                   0
2   1           Cat 2   Cat 1 > Cat 2           0
3   2           Cat 3   Cat 1 > Cat 2 > Cat 3   1
4   0           Cat A   Cat A                   0
5   4           Cat B   Cat A > Cat B           1

Now I want to fetch all the hierarchy for id: 3

What I have tried so far is:

with recursive cte (id, name, parentId) AS (
    select
        id,
        name,
        parentId
    from
        categories
    where
        parentId = 1
    union
    all
    select
        c.id,
        c.name,
        c.parentId
    from
        categories c
        inner join cte on c.parentId = cte.id
)
select
    *
from
    cte;

The above query returns:

[
    {
        id: 1,
        parentId: 0,
        name: Cat 1,
        path: Cat 1
    },
    {
        id: 2,
        parentId: 1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    }
]

But I want this:

[
    {
        id: 1,
        parentId: 0,
        name: Cat 1,
        path: Cat 1
    },
    {
        id: 2,
        parentId: 1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    },
    {
        id: 3,
        parentId: 2,
        name: Cat 3,
        path: Cat 1 > Cat 2 > Cat 3
    }
]

If I provide id: 2, in that case I am expecting:

[
    {
        id: 1,
        parentId: 0,
        name: Cat 1,
        path: Cat 1
    },
    {
        id: 2,
        parentId: 1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    }
]

There is something that I am doing wrong with the query, can anyone identify?

EDIT

Here is reproduced scenario: https://dbfiddle.uk/llwsSBEu

Answer 1

In below script when your input is 1. It will return the child rows with their calculated paths.

WITH recursive cte (id, name, parentId, path)
AS (SELECT
          id,
          name,
          parentId,
          name AS path
    FROM  categories
    WHERE id = 1
    UNION ALL /*find the items where this is the parent for*/
    SELECT
         c.id,
         c.name,
         c.parentId,
         CONCAT(cte.path, '>', c.name) AS path
    FROM categories c
         INNER JOIN cte ON c.parentId = cte.id)
SELECT
         cte.id,
         cte.name,
         cte.parentId,
         cte.path
FROM     cte
ORDER BY id ASC;

In script below, you make use of the path column you have added in your demo script, but your input will be the destination and it will give the rows which have created this.

WITH recursive cte (id, name, parentId, path, pathfornext)
AS (SELECT
          id,
          name,
          parentId,
          CAST(path AS CHAR(1000)),
          CAST(REPLACE(path, CONCAT(' > ', name), '') AS CHAR(1000)) AS pathfornext
    FROM  categories
    WHERE id = 3
    UNION ALL /*find the items where this is the child for*/
    SELECT
         c.id,
         c.name,
         c.parentId,
         cte.pathfornext,
         CAST(REPLACE(cte.pathfornext, CONCAT(' > ', c.name), '') AS CHAR(1000)) AS pathfornext
    FROM categories c
         INNER JOIN cte ON c.Id = cte.ParentId)
SELECT
         cte.id,
         cte.name,
         cte.parentId,
         cte.path
FROM     cte
ORDER BY id ASC;