Managing Hierarchical Data in MySQL using The Adjacency List Model

Question

I would like to retrieve all categories with parent in order to create a breadcrumb path. for that reason I create the following schema:

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);
INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

With the example I follow I can retrieve the information using the following SQL:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

The problem I found is before being able to see the full path of a category we have to know the level at which it resides.

my question, is there a way to retrieve the information as the following example but not defining the exact amount of levels?

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

my idea is to retrieve lev1 or lev2 etc taking into consideration the depth level.

UPDATE:

EXAMPLE OUTPUT

ELECTRONICS
ELECTRONICS / TELEVISIONS
ELECTRONICS / TELEVISIONS / TUBE
ELECTRONICS / TELEVISIONS / LCD
ETC
ETC

MySQL doesn't support recursive hierarchical queries. So AFAIK in MySQL you would need to know how many levels deep to query, and how many columns you want in your wide output format. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 11, 2017 at 6:47

StanislavL · Accepted Answer · 2017-04-11 06:54:06Z

1

There is a workaround.

You can add collateral PATH column. The column should have chain of ids from the elemetn to parent. Thus for the root the column empty. All the children of root has _

+-------------+----------------------+--------+--------+
| category_id | name                 | parent | path   |
+-------------+----------------------+--------+--------+
|           1 | ELECTRONICS          |   NULL |        |
|           2 | TELEVISIONS          |      1 |1_      |
|           3 | TUBE                 |      2 |1_2_    |
|           4 | LCD                  |      2 |1_2_    |
|           5 | PLASMA               |      2 |1_2_    |
|           6 | PORTABLE ELECTRONICS |      1 |1_      |
|           7 | MP3 PLAYERS          |      6 |1_6_    |
|           8 | FLASH                |      7 |1_6_7_  |
|           9 | CD PLAYERS           |      6 |1_6_    |
|          10 | 2 WAY RADIOS         |      6 |1_6_    |
+-------------+----------------------+--------+--------+

On insert a new node you just copy parent node path and add '_' So to retrieve all children of a node you just use

SELECT * 
FROM THE_TABLE
WHERE PATH LIKE '<parent node path>%'

There is a restriction of the field size and amount of levels though

answered Apr 11, 2017 at 6:54

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

s_h Over a year ago

so including the complete path. but its not possible to use the parentid as the recursive information? thank you in advance

Tim Biegeleisen Over a year ago

This is a clever workaround, but with the caviat that the path itself would be hard to maintain.

StanislavL Over a year ago

@TimBiegeleisen not hard to maintain. It could be done by trigger ON PARENT_ID change or to remove the PARENT_ID totally and use the PATH instead.

Collectives™ on Stack Overflow

Managing Hierarchical Data in MySQL using The Adjacency List Model

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related