How to get last child relation in mysql

Question

My table structure:

--------------------------
|Categories              |
--------------------------
|id    |parent_id|title  |
--------------------------
|1     |null     |t1     |
--------------------------
|2     |1        |t2     |
--------------------------
|3     |1        |t3     |
--------------------------
|4     |2        |t4     |
--------------------------
|5     |4        |t5     |
--------------------------
|6     |null     |t6     |
--------------------------

I need to find out how many items are between a given item/category and its root item/category including the specified item and the root item.

For example in this table title=t5 would have a count of 4 (t1->t2->t4->t5).

MySQL Version: 5.6.21

@AliMohammadi Would you consider update mysql version to support `cte` ? cte recursion will be easier to make it. — D-Shih, Aug 24 '18 at 10:32
@D-Shih i cant because on the host server. but can u tell me how can do it by cte? — Ali Mohammadi, Aug 24 '18 at 10:34

D-Shih · Answer 1 · 2018-08-24T10:48:03.463

1

If your mysql version support cte you can try to use CTE Recursion make it.

Schema (MySQL v8.0)

CREATE TABLE  Categories(
   id INT,
   parent_id INT,
   title VARCHAR(50)
);



INSERT INTO Categories VALUES (1,null,'t1');
INSERT INTO Categories VALUES (2,1   ,'t2');
INSERT INTO Categories VALUES (3,1   ,'t3');
INSERT INTO Categories VALUES (4,2   ,'t4');
INSERT INTO Categories VALUES (5,4   ,'t5');
INSERT INTO Categories VALUES (6,null,'t6');

Query #1

WITH RECURSIVE  cte1 AS (
  SELECT id,parent_id,title
  FROM Categories
  where title = 't5'
  UNION ALL
  SELECT cte1.id,c.parent_id,c.title
  FROM cte1 INNER JOIN Categories c
  on c.id = cte1.parent_id
)
SELECT id,GROUP_CONCAT(title separator '->') result
FROM cte1
GROUP BY id;

| id  | result         |
| --- | -------------- |
| 5   | t5->t4->t2->t1 |

View on DB Fiddle

edited Aug 24 '18 at 10:48

answered Aug 24 '18 at 10:44

D-Shih

44,943
6
31
51

@AliMohammadi No problem – D-Shih Aug 24 '18 at 10:48
1

@AliMohammadi If your can't use `CTE`, you only use `while` with temp table to make it https://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes/5291159#5291159 – D-Shih Aug 24 '18 at 10:50
oom...good query but need query without set condition of colume (`where title = 't5'`) – Ali Mohammadi Aug 24 '18 at 15:04
@AliMohammadi just use `parent_id is not null` instead of `title = 't5'` https://www.db-fiddle.com/f/f9YUnKk6tBzH3LfxYuKvQi/2 – D-Shih Aug 24 '18 at 15:16

score 0 · Answer 2 · answered Aug 24 '18 at 12:51

In MySQL < v8.0 your only real choice is a stored function. I think this one will do the job for you; it emulates recursing back through the table from a child until it finds an item with no parent (parent_id is null):

DROP FUNCTION IF EXISTS depth;
DELIMITER \\
CREATE FUNCTION `depth`(item VARCHAR(20)) RETURNS int(11)
   DETERMINISTIC
BEGIN
 DECLARE d INT DEFAULT 1;
 DECLARE p INT;
 SELECT COALESCE(parent_id, 0) INTO p FROM categories WHERE title = item;
 WHILE p != 0 DO
   SET d = d + 1;
   SELECT COALESCE(parent_id, 0) INTO p FROM categories WHERE id = p;
 END WHILE;
 RETURN (d);
END \\
DELIMITER ;

SELECT depth('t1'), depth('t2'), depth('t3'), depth('t4'), depth('t5'), depth('t6')

Output:

depth('t1')     depth('t2')     depth('t3')     depth('t4')     depth('t5')     depth('t6')     
1               2               2               3               4               1

How to get last child relation in mysql

2 Answers2