Query the database for building a list of paths

Blog Databases MySQL building a list of paths mysql, sql breadcumbs, sql build path brewedbrilliance.net July 28, 2024 0 Comments

Query the database for building a list of paths

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.