I’m trying to make a breadcrumb for a ecommerce website. Something like this:
<code>Home > Kitchen > Serving and reception > Spoon
</code>
<code>Home > Kitchen > Serving and reception > Spoon
</code>
Home > Kitchen > Serving and reception > Spoon
Each of those item are a tag. So, the tags
table is parent-based/historical. Here is a sample of the table structure:
<code>// tags
+----+-----------------------+-----------+
| id | name | parent_id |
+----+-----------------------+-----------+
| 1 | Home | Null |
| 2 | Kitchen | 1 |
| 3 | Serving and reception | 2 |
| 4 | Spoon | 3 |
| 5 | Digital | NULL |
| 6 | Communication | 5 |
| 7 | Cellphone | 6 |
| .. | ... | ... |
+----+-----------------------+-----------+
</code>
<code>// tags
+----+-----------------------+-----------+
| id | name | parent_id |
+----+-----------------------+-----------+
| 1 | Home | Null |
| 2 | Kitchen | 1 |
| 3 | Serving and reception | 2 |
| 4 | Spoon | 3 |
| 5 | Digital | NULL |
| 6 | Communication | 5 |
| 7 | Cellphone | 6 |
| .. | ... | ... |
+----+-----------------------+-----------+
</code>
// tags
+----+-----------------------+-----------+
| id | name | parent_id |
+----+-----------------------+-----------+
| 1 | Home | Null |
| 2 | Kitchen | 1 |
| 3 | Serving and reception | 2 |
| 4 | Spoon | 3 |
| 5 | Digital | NULL |
| 6 | Communication | 5 |
| 7 | Cellphone | 6 |
| .. | ... | ... |
+----+-----------------------+-----------+
In this case, it’s very hard to fetch all nested-relate-items in a correct order. Should I change the database structure? I mean, what’s the best approach to have such a data model?
Noted that, currently I use a PHP-loop to fetch the related tags and make that breadcrumb. Also, I use MySQL ver 8.0.37
.
5
You can use recursive CTE like this:
<code>WITH RECURSIVE TagHierarchy AS (
SELECT id, name, parent_id, name AS path
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, t.parent_id,
CONCAT(th.path, ' > ', t.name) AS path
FROM tags t
JOIN TagHierarchy th ON t.parent_id = th.id
)
SELECT id, name, path
FROM TagHierarchy
ORDER BY path;
</code>
<code>WITH RECURSIVE TagHierarchy AS (
SELECT id, name, parent_id, name AS path
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, t.parent_id,
CONCAT(th.path, ' > ', t.name) AS path
FROM tags t
JOIN TagHierarchy th ON t.parent_id = th.id
)
SELECT id, name, path
FROM TagHierarchy
ORDER BY path;
</code>
WITH RECURSIVE TagHierarchy AS (
SELECT id, name, parent_id, name AS path
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.name, t.parent_id,
CONCAT(th.path, ' > ', t.name) AS path
FROM tags t
JOIN TagHierarchy th ON t.parent_id = th.id
)
SELECT id, name, path
FROM TagHierarchy
ORDER BY path;
Result
<code>+----+-----------------------+----------------------------------+
| id | name | path |
+----+-----------------------+----------------------------------+
| 1 | Home | Home |
| 2 | Kitchen | Home > Kitchen |
| 3 | Serving and reception | Home > Kitchen > Serving and reception |
| 4 | Spoon | Home > Kitchen > Serving and reception > Spoon |
| 5 | Digital | Digital |
| 6 | Communication | Digital > Communication |
| 7 | Cellphone | Digital > Communication > Cellphone |
+----+-----------------------+----------------------------------+
</code>
<code>+----+-----------------------+----------------------------------+
| id | name | path |
+----+-----------------------+----------------------------------+
| 1 | Home | Home |
| 2 | Kitchen | Home > Kitchen |
| 3 | Serving and reception | Home > Kitchen > Serving and reception |
| 4 | Spoon | Home > Kitchen > Serving and reception > Spoon |
| 5 | Digital | Digital |
| 6 | Communication | Digital > Communication |
| 7 | Cellphone | Digital > Communication > Cellphone |
+----+-----------------------+----------------------------------+
</code>
+----+-----------------------+----------------------------------+
| id | name | path |
+----+-----------------------+----------------------------------+
| 1 | Home | Home |
| 2 | Kitchen | Home > Kitchen |
| 3 | Serving and reception | Home > Kitchen > Serving and reception |
| 4 | Spoon | Home > Kitchen > Serving and reception > Spoon |
| 5 | Digital | Digital |
| 6 | Communication | Digital > Communication |
| 7 | Cellphone | Digital > Communication > Cellphone |
+----+-----------------------+----------------------------------+
2