0
0
Read Time:3 Minute, 27 Second
In this small “how to” we are going to show how to query the database for building a list of paths.
Suppose we have an SQL table categories where we are storing the id of the category, the name and the parent category id that is a reference to the id of another record. Imagine we have this data in our table
+----+-------------+-----------+---------------------+
| id | name | parent_id | created_at |
+----+-------------+-----------+---------------------+
| 1 | Shop | NULL | 2024-07-28 11:29:02 |
| 2 | Electronics | NULL | 2024-07-28 11:29:02 |
| 3 | Computer | 1 | 2024-07-28 11:29:02 |
| 4 | Tablet | 1 | 2024-07-28 11:29:02 |
| 5 | Laptop | 3 | 2024-07-28 11:29:02 |
| 6 | Mac | 3 | 2024-07-28 11:29:02 |
| 7 | MacBook Pro | 6 | 2024-07-28 11:29:02 |
| 8 | Mac mini | 6 | 2024-07-28 11:29:02 |
| 9 | AMD | 5 | 2024-07-28 11:29:02 |
| 10 | Intel | 5 | 2024-07-28 11:29:02 |
| 11 | Hardware | 2 | 2024-07-28 11:29:02 |
| 12 | Video Cards | 11 | 2024-07-28 11:29:02 |
| 13 | CPU | 11 | 2024-07-28 11:29:02 |
| 14 | Nvidia | 12 | 2024-07-28 11:29:02 |
| 15 | AMD | 12 | 2024-07-28 11:29:02 |
+----+-------------+-----------+---------------------+
In the above table we have categories and subcategories where the parent_id column contains the id of the parent category (i.e. Hardware has parent_id=2 that is Electronics ).
We want to write a query that will return a list more or less like
+----+-----------+-------------+--------------------------------------+
| id | parent_id | name | full_path |
+----+-----------+-------------+--------------------------------------+
| 1 | NULL | Shop | Shop |
| 2 | NULL | Electronics | Electronics |
| 3 | 1 | Computer | Shop > Computer |
| 4 | 1 | Tablet | Shop > Tablet |
| 5 | 3 | Laptop | Shop > Computer > Laptop |
| 6 | 3 | Mac | Shop > Computer > Mac |
| 7 | 6 | MacBook Pro | Computer > Mac > MacBook Pro |
| 8 | 6 | Mac mini | Computer > Mac > Mac mini |
| 9 | 5 | AMD | Computer > Laptop > AMD |
| 10 | 5 | Intel | Computer > Laptop > Intel |
| 11 | 2 | Hardware | Electronics > Hardware |
| 12 | 11 | Video Cards | Electronics > Hardware > Video Cards |
| 13 | 11 | CPU | Electronics > Hardware > CPU |
| 14 | 12 | Nvidia | Hardware > Video Cards > Nvidia |
| 15 | 12 | AMD | Hardware > Video Cards > AMD |
+----+-----------+-------------+--------------------------------------+Let’s start from creating the table an populating the data
CREATE TABLE IF NOT EXISTS categories (
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 (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO categories (`name`,`parent_id`) VALUES ( 'Shop', null),
( 'Electronics', null),
( 'Computer', 1),
( 'Tablet', 1),
( 'Laptop', 3),
( 'Mac', 3),
( 'MacBook Pro', 6),
( 'Mac mini', 6),
( 'AMD', 5),
( 'Intel', 5),
( 'Hardware', 2),
( 'Video Cards', 11),
( 'CPU', 11),
( 'Nvidia', 12),
( 'AMD', 12);The query we are looking for is this
SELECT
id,
parent_id,
name,
CONCAT(COALESCE((SELECT CONCAT(c3.name,' > ') FROM categories c3 WHERE c3.id=(SELECT c4.parent_id FROM categories c4 WHERE c4.id=c1.parent_id)),''),
COALESCE((SELECT CONCAT(c2.name,' > ') FROM categories c2 WHERE c2.id=c1.parent_id),''),
name) as full_path
FROM categories c1;
# OR if we want to use '/' as path separator
SELECT
id,
parent_id,
name,
CONCAT(COALESCE((SELECT CONCAT(c3.name,'/') FROM categories c3 WHERE c3.id=(SELECT c4.parent_id FROM categories c4 WHERE c4.id=c1.parent_id)),''),
COALESCE((SELECT CONCAT(c2.name,'/') FROM categories c2 WHERE c2.id=c1.parent_id),''),
name) as full_path
FROM categories c1;
# RESULT
+----+-----------+-------------+----------------------------------+
| id | parent_id | name | full_path |
+----+-----------+-------------+----------------------------------+
| 1 | NULL | Shop | Shop |
| 2 | NULL | Electronics | Electronics |
| 3 | 1 | Computer | Shop/Computer |
| 4 | 1 | Tablet | Shop/Tablet |
| 5 | 3 | Laptop | Shop/Computer/Laptop |
| 6 | 3 | Mac | Shop/Computer/Mac |
| 7 | 6 | MacBook Pro | Computer/Mac/MacBook Pro |
| 8 | 6 | Mac mini | Computer/Mac/Mac mini |
| 9 | 5 | AMD | Computer/Laptop/AMD |
| 10 | 5 | Intel | Computer/Laptop/Intel |
| 11 | 2 | Hardware | Electronics/Hardware |
| 12 | 11 | Video Cards | Electronics/Hardware/Video Cards |
| 13 | 11 | CPU | Electronics/Hardware/CPU |
| 14 | 12 | Nvidia | Hardware/Video Cards/Nvidia |
| 15 | 12 | AMD | Hardware/Video Cards/AMD |
+----+-----------+-------------+----------------------------------+This query can be really useful for building for example paths, or navigation path in your shopping website.
Happy
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
Share this content: