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

categories table :

select *  from categories;

Expected Output -

select * , 'some path sql' as path from categories;

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.

CodePudding user response：

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