Mysql - Retreive all child categories - sql query

StormTrooper
November 11, 2022
150 views
3 votes
3 Answers

I have Categories stored in a single table.

Where there is no limit on number of childerns.

I want to fetch all the linked childern 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                         
A1   null       Cat 1   Cat 1                   
A2   A1         Cat 2   Cat 1 > Cat 2           
A3   A2         Cat 3   Cat 1 > Cat 2 > Cat 3   
A4   null       Cat A   Cat A                   
A5   A4         Cat B   Cat A > Cat B

Now I want to fetch hierarchy for id: 1

What I have tried so far is:

with recursive cte (id, name, parentId) AS (
    select
        id,
        name,
        parentId
    from
        categories
    where
        parentId = 'A1'
    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: A1,
        parentId: null,
        name: Cat 1,
        path: Cat 1
    },
    {
        id: A2,
        parentId: A1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    }
]

But I want this:

[
    {
        id: A2,
        parentId: A1,
        name: Cat 2,
        path: Cat 1 > Cat 2
    },
    {
        id: A3,
        parentId: A2,
        name: Cat 3,
        path: Cat 1 > Cat 2 > Cat 3
    }
]

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

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

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

Here is reproduced scenario: https://dbfiddle.uk/Beefs-UH

IMPORTANT NOTE:
The primary key i.e id is a unique identifier string not an integer. So the records cannot be sorted on id.

Answers

You left out the root node from your result set by applying a filter on the "parentId" column. (Your root has no parent.)

Here’s an aproach that works:

Use your desired id for filtering your initial step. (I used your first example, id = 3)
Follow the "parent-chain" until root is reached.
(Cat 3’s parent is Cat 2, Cat 2’s parent is Cat 1, and Cat 1 has no parent: root is reached.)
Run a simple select.

WITH RECURSIVE cte (id, name, parentId, path, lvl) AS (
  -- Initial step
  SELECT
     id,
     name,
     parentId,
     path,
     1
  FROM categories
  WHERE id = 3

  UNION ALL

  -- Follow the "parent-chain"
  SELECT
     cat.id,
     cat.name,
     cat.parentId,
     cat.path,
     cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.parentId = cat.id
)

SELECT id, name, parentId, path
FROM cte
ORDER BY lvl DESC
;

But since you want to maintain the "path" field, it might be easier to calculate all paths by using a recursive CTE and then check for mismatch:

WITH RECURSIVE cte (root, id, name, parentId, old_path, new_path, lvl) AS (
  SELECT
      id,
      id,
      name,
      parentId,
      path,
      name,
      1
  FROM categories
  WHERE parentId = 0

  UNION ALL

  SELECT
      cte.root,
      cat.id,
      cat.name,
      cat.parentId,
      cat.path,
      CONCAT(cte.name, ' > ', cat.name),
      cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.id = cat.parentId
)
  
SELECT *
FROM cte
WHERE old_path != new_path
ORDER BY root, lvl
;

Solution for your after-edit problem:

WITH RECURSIVE cte (id, name, parentId, path, lvl) AS (
  SELECT
     id,
     name,
     parentId,
     path,
     1
  FROM categories
  WHERE id = 2

  UNION ALL

  SELECT
     cat.id,
     cat.name,
     cat.parentId,
     cat.path,
     cte.lvl + 1
  FROM cte
  INNER JOIN categories cat
    ON cte.id = cat.parentId
)

SELECT id, name, parentId, path
FROM cte
ORDER BY lvl ASC
;

https://dbfiddle.uk/agO_kNXf

To get the up-hierarchy try the following:

with recursive cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
)
select * from cte
order by ord desc;

Here, we order the results by the generated ‘ord’ value, you don’t need to order by the id.

To get the down-hierarchy, use the following:

with recursive cte (id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on c.parentId = t.id 
)
select * from cte
order by ord;

To get the full hierachy (up and down), you can union the results of the both recursive queries as the following:

with recursive cte(id, name, parentId, path, ord) as 
(
  select id, name, parentId, path, 1 as ord
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, t.ord+1
  from categories c join cte t
  on t.parentId = c.id 
),
cte2(id, name, parentId, path, ord) as 
(
  select id, name, parentId, path,0 as ord 
  from  categories
  where id = 'A2'
  union all
  select c.id, c.name, c.parentId, c.path, ord-1
  from categories c join cte2 t
  on c.parentId = t.id 
)
select id, name, parentId, path from
(
  select *  from cte
  union all
  select *  from cte2
) T
where ord <> 0 /*to avoid id = 'A2' duplication (one from cte and one from cte2)*/
order by ord desc

Since you are storing the full path for each id, you can try another approach using self-join as the following:

For up-hierarchy:

select C1.id, C1.parentId, C1.name, C1.path
from categories C1 join categories C2
on C2.path like CONCAT('%', C1.name, '%')
where C2.id='A2'
order by length(C1.path)

Here, we used the length(path) to order the results, this will guarantee that the most upper parent appears first and the lowest child appears last.

For down-hierarchy, use on C1.path like CONCAT('%', C2.name, '%').

For full hierarchy, use on C1.path like CONCAT('%', C2.name, '%') or C2.path like CONCAT('%', C1.name, '%').

See a demo on MySQL.

See a demo on SQL Server (with minor modifications).

The data in categories table represents a tree structure connected by nodes (category):

a node could have zero or more child nodes; and
a node could have zero or one parent node.

Step 1. Create the table schema (with slight modification)

-- create table
create table tree (
    id          int,
    parent_id   int,
    name        varchar(100),
    path        varchar(200)
);

Step 2. Generate test data and each node could have zero to three children nodes (Please feel free to modify CTE to include more union alls.

insert into tree
with recursive cte (parent_id, id, name, path) as (
    select 0                      as parent_id, 
           1                      as id,
           convert(1, char(100))  as name,
           convert(1, char(100))  as path
    union all
    select id                     as parent_id,
           id*10 + 1              as id,
           convert(id*10+1, char) as name,
           concat(path,'>',convert(id*10+1, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
    union all
    select id                     as parent_id,
           id*10 + 2              as id,
           convert(id*10+2, char) as name,
           concat(path,'>',convert(id*10+2, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
    union all
    select id                     as parent_id,
           id*10 + 3              as id,
           convert(id*10+3, char) as name,
           concat(path,'>',convert(id*10+3, char)) as path
      from cte
     where id < 1000
       and floor(rand()*2) = 1
)
select id, parent_id, name, path
  from cte
 order by 1,2;

My test data looks like this (YMMV because of RAND() function):

id  |parent_id|name|path         |
----+---------+----+-------------+
   1|        0|1   |1            |
  11|        1|11  |1>11         |
  12|        1|12  |1>12         |
  13|        1|13  |1>13         |
 112|       11|112 |1>11>112     |
 121|       12|121 |1>12>121     |
 122|       12|122 |1>12>122     |
 123|       12|123 |1>12>123     |
 132|       13|132 |1>13>132     |
 133|       13|133 |1>13>133     |
1121|      112|1121|1>11>112>1121|
1211|      121|1211|1>12>121>1211|
1221|      122|1221|1>12>122>1221|
1222|      122|1222|1>12>122>1222|
1233|      123|1233|1>12>123>1233|
1321|      132|1321|1>13>132>1321|
1332|      133|1332|1>13>133>1332|
1333|      133|1333|1>13>133>1333|

The tree can be visualized as below:

Step 3. Retrieve all linked parent nodes and children nodes (ie. ID=12)

-- get all linked parent & child nodes
with recursive cte_tree_parent (id, parent_id, name, path) as (
    select id, 
           parent_id,
           name,
           path
      from tree
     where id = 12
     union all
    select p.id,
           p.parent_id,
           p.name,
           p.path
      from cte_tree_parent c  
      join tree p      
        on c.parent_id = p.id),
cte_tree_child (id, parent_id, name, path) as (
    select id, 
           parent_id,
           name,
           path
      from tree
     where id = 12
     union all
    select c.id,
           c.parent_id,
           c.name,
           c.path
      from cte_tree_child p  
      join tree c      
        on p.id = c.parent_id)
select id, parent_id, name, path
  from cte_tree_parent
 union
select id, parent_id, name, path
  from cte_tree_child
 order by path;

Result:

id  |parent_id|name|path         |
----+---------+----+-------------+
   1|        0|1   |1            |
  12|        1|12  |1>12         |
 121|       12|121 |1>12>121     |
1211|      121|1211|1>12>121>1211|
 122|       12|122 |1>12>122     |
1221|      122|1221|1>12>122>1221|
1222|      122|1222|1>12>122>1222|
 123|       12|123 |1>12>123     |
1233|      123|1233|1>12>123>1233|

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Retreive all child categories – sql query

Answers