On gists

Advanced CTE - Tree

MySql MySql tricks MySql - advanced

tree.sql Raw #

https://akki.ca/blog/mysql-adjacency-list-model-for-hierarchical-data-using-cte/


CREATE TABLE category (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  parent_id int(10) UNSIGNED DEFAULT NULL,
  is_active int(1) UNSIGNED DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO category (name, parent_id) VALUES ("Electronics", null);
INSERT INTO category (name, parent_id) VALUES ("TV", 1);
INSERT INTO category (name, parent_id) VALUES ("Smart", 2);
INSERT INTO category (name, parent_id) VALUES ("4K Ultra HD", 2);
INSERT INTO category (name, parent_id) VALUES ("Curved", 2);
INSERT INTO category (name, parent_id) VALUES ("Camera", 1);
INSERT INTO category (name, parent_id) VALUES ("Computer", 1);
INSERT INTO category (name, parent_id) VALUES ("Desktop", 7);
INSERT INTO category (name, parent_id) VALUES ("Laptops", 7);
INSERT INTO category (name, parent_id) VALUES ("Work", 9);
INSERT INTO category (name, parent_id) VALUES ("Travel", 9);
INSERT INTO category (name, parent_id) VALUES ("All Around", 9);
INSERT INTO category (name, parent_id) VALUES ("Gaming", 9);
INSERT INTO category (name, parent_id) VALUES ("Tablet", 7);


/* Tree */
WITH RECURSIVE shoppingCategories AS
(
  SELECT id, name, parent_id, 1 AS depth, name AS path
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, sc.depth + 1, CONCAT(sc.path, ' > ', c.name)
    FROM shoppingCategories AS sc 
      JOIN category AS c ON sc.id = c.parent_id
)
SELECT * FROM shoppingCategories;


+------+-------------+-----------+-------+-----------------------------------------------+
| id   | name        | parent_id | depth | path                                          |
+------+-------------+-----------+-------+-----------------------------------------------+
|    1 | Electronics |      NULL |     1 | Electronics                                   |
|    2 | TV          |         1 |     2 | Electronics > TV                              |
|    6 | Camera      |         1 |     2 | Electronics > Camera                          |
|    7 | Computer    |         1 |     2 | Electronics > Computer                        |
|    3 | Smart       |         2 |     3 | Electronics > TV > Smart                      |
|    4 | 4K Ultra HD |         2 |     3 | Electronics > TV > 4K Ultra HD                |
|    5 | Curved      |         2 |     3 | Electronics > TV > Curved                     |
|    8 | Desktop     |         7 |     3 | Electronics > Computer > Desktop              |
|    9 | Laptops     |         7 |     3 | Electronics > Computer > Laptops              |
|   14 | Tablet      |         7 |     3 | Electronics > Computer > Tablet               |
|   10 | Work        |         9 |     4 | Electronics > Computer > Laptops > Work       |
|   11 | Travel      |         9 |     4 | Electronics > Computer > Laptops > Travel     |
|   12 | All Around  |         9 |     4 | Electronics > Computer > Laptops > All Around |
|   13 | Gaming      |         9 |     4 | Electronics > Computer > Laptops > Gaming     |
+------+-------------+-----------+-------+-----------------------------------------------+






/* ancestors, tree */
SET @catID = 9;
WITH RECURSIVE
  ancestorCategories  AS
  (
    SELECT id, name, parent_id, 1 AS depth
      FROM category
      WHERE id = @catID
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ac.depth - 1
      FROM ancestorCategories AS ac
        JOIN category AS c ON ac.parent_id = c.id
  ),
  ancestorDeptOneCategories  AS
  (
    SELECT id, name, parent_id, 1 AS depth
      FROM category
      WHERE parent_id IS null
    UNION ALL
    SELECT c.id, c.name, c.parent_id, adoc.depth + 1
      FROM ancestorDeptOneCategories AS adoc
        JOIN ancestorCategories ac ON ac.id = adoc.id
        JOIN category AS c ON adoc.id = c.parent_id
  )
SELECT * FROM ancestorDeptOneCategories
ORDER BY depth;


+------+-------------+-----------+-------+
| id   | name        | parent_id | depth |
+------+-------------+-----------+-------+
|    1 | Electronics |      NULL |     1 |
|    2 | TV          |         1 |     2 |
|    6 | Camera      |         1 |     2 |
|    7 | Computer    |         1 |     2 |
|    8 | Desktop     |         7 |     3 |
|    9 | Laptops     |         7 |     3 |
|   14 | Tablet      |         7 |     3 |
|   10 | Work        |         9 |     4 |
|   11 | Travel      |         9 |     4 |
|   12 | All Around  |         9 |     4 |
|   13 | Gaming      |         9 |     4 |
+------+-------------+-----------+-------+