0

I have categories table where parent_id is self referencing foreign key.

categories table :

select *  from categories;
category_id name parent_id
1 T1 null
2 T2 null
3 T11 1
4 T12 1
5 T111 3
6 T112 3
7 T1121 6
8 T1122 6
9 T121 4
10 T122 4
11 T21 2
12 T211 11
13 T212 11
14 T2111 12

Expected Output -

select * , 'some path sql' as path from categories;
category_id name parent_id path
1 T1 null T1
2 T2 null T2
3 T11 1 T1 -> T11
4 T12 1 T1 -> T12
5 T111 3 T1 -> T11 -> T111
6 T112 3 T1 -> T11 -> T112
7 T1121 6 T1 -> T11 -> T112 -> T1121
8 T1122 6 T1 -> T11 -> T112 -> T1122
9 T121 4 T1 -> T12 -> T121
10 T122 4 T1 -> T12 -> T122
11 T21 2 T2 -> T21
12 T211 11 T1 -> T21 -> T211
13 T212 11 T1 -> T21 -> T212
14 T2111 12 T1 -> T21 -> T211 -> T2111

What i have tried :

I have tried this query but its return 38 rows. I want actual 14 rows.

WITH RECURSIVE cte_name AS (
    select category_id, name, parent_id , name AS path from  categories 
    UNION ALL
    select c.category_id,c.name,c.parent_id, concat_ws(' -> ', cte_name.path,  c.name) 
    from categories as c
    INNER JOIN cte_name  ON cte_name.category_id = c.parent_id  
)
SELECT * FROM cte_name;

I have no idea how to write exact query that will return actual all 14 rows with its path.

Dhairya Lakhera
  • 4,445
  • 3
  • 35
  • 62
  • Does this answer your question? [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) –  Apr 28 '22 at 07:38
  • @Kendle this answer is to get childrens using recursive cte for a particular parent . my query is to get all the records in the table with there path. I have no idea how to write such query. I can write recursive cte but unable to find the solution for path. – Dhairya Lakhera Apr 28 '22 at 07:43
  • use `concat(parent_path,separator,child_id)` in the CTE to build the path? –  Apr 28 '22 at 07:55
  • I tried it.. it is returning 38 rows. i am editing my answer with what i have tried. i want just 14 actuall rows. – Dhairya Lakhera Apr 28 '22 at 07:58
  • please could you give the table definition and sample data, preferably as a dbfiddle –  Apr 28 '22 at 08:32
  • @Kendle https://www.db-fiddle.com/f/jhP7NvCzHKwaqbTPXzNNJK/0 – Dhairya Lakhera Apr 28 '22 at 08:44

1 Answers1

2

You can use CONCAT(parent_path,separator,child_id) in the CTE to build the path. You need to specify 'WHERE parent_id IS NULL` in the first part of the CTE before the UNION so that we only include paths starting from the roots.

CREATE TABLE IF NOT EXISTS categories (
    category_id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    parent_id int(11) DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (category_id),
    FOREIGN KEY (parent_id) REFERENCES categories(category_id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1', null);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T2', null);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T11', 1);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T12', 1);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T111', 3);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T112', 3);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1121', 6);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T1122', 6);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T121', 4);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T122', 4);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T21', 2);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T211', 11);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T212', 11);
INSERT INTO categories (`name`,`parent_id`) VALUES ( 'T2111', 12);

WITH RECURSIVE cte_name AS (
    select category_id, name, parent_id , name AS path from  categories 
    where parent_id is null
    UNION ALL
    select c.category_id,c.name,c.parent_id, concat_ws(' -> ', cte_name.path,  c.name) 
    from categories as c
    INNER JOIN cte_name  ON cte_name.category_id = c.parent_id  
)
SELECT * FROM cte_name;

category_id | name  | parent_id | path                      
----------: | :---- | --------: | :-------------------------
          1 | T1    |      null | T1                        
          2 | T2    |      null | T2                        
          3 | T11   |         1 | T1 -> T11                 
          4 | T12   |         1 | T1 -> T12                 
         11 | T21   |         2 | T2 -> T21                 
          5 | T111  |         3 | T1 -> T11 -> T111         
          6 | T112  |         3 | T1 -> T11 -> T112         
          9 | T121  |         4 | T1 -> T12 -> T121         
         10 | T122  |         4 | T1 -> T12 -> T122         
         12 | T211  |        11 | T2 -> T21 -> T211         
         13 | T212  |        11 | T2 -> T21 -> T212         
          7 | T1121 |         6 | T1 -> T11 -> T112 -> T1121
          8 | T1122 |         6 | T1 -> T11 -> T112 -> T1122
         14 | T2111 |        12 | T2 -> T21 -> T211 -> T2111

db<>fiddle here