I have the following tables:
<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 |
+----+-----------------------+-----------+
// products
+----+------------------------------------------+--------+
| id | name | tag_id | -- this is the deepest tag id
+----+------------------------------------------+--------+
| 1 | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4 |
| 2 | iPhone 14 Promax | 7 |
+----+------------------------------------------+--------+
</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 |
+----+-----------------------+-----------+
// products
+----+------------------------------------------+--------+
| id | name | tag_id | -- this is the deepest tag id
+----+------------------------------------------+--------+
| 1 | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4 |
| 2 | iPhone 14 Promax | 7 |
+----+------------------------------------------+--------+
</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 |
+----+-----------------------+-----------+
// products
+----+------------------------------------------+--------+
| id | name | tag_id | -- this is the deepest tag id
+----+------------------------------------------+--------+
| 1 | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4 |
| 2 | iPhone 14 Promax | 7 |
+----+------------------------------------------+--------+
I need to implement a listing page per “tag”. So, I need to get “iPhone 14 Promax” by each of the following tags.ids: 5, 6, 7 (since they are all related to that product)
Any idea by what query can I get product#2 (iPhone 14 Promax) by having tag#6 (Communication)?
Noted that, I can get product#2 by having tag#7 by a simple join:
<code>SELECT p.*
FROM products p
JOIN tags t
ON t.id = p.tag_id
WHERE t.id = 7
</code>
<code>SELECT p.*
FROM products p
JOIN tags t
ON t.id = p.tag_id
WHERE t.id = 7
</code>
SELECT p.*
FROM products p
JOIN tags t
ON t.id = p.tag_id
WHERE t.id = 7
But I don’t know how can I get the product by higher level tag ids.
2
On MySQL 8+, we can use a recursive CTE:
<code>WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
SELECT id, name, parent_id, id AS orig_id
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
FROM tags t1
INNER JOIN cte t2
ON t2.id = t1.parent_id
)
SELECT MAX(p.name) AS name
FROM cte t
LEFT JOIN products p
ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;
</code>
<code>WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
SELECT id, name, parent_id, id AS orig_id
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
FROM tags t1
INNER JOIN cte t2
ON t2.id = t1.parent_id
)
SELECT MAX(p.name) AS name
FROM cte t
LEFT JOIN products p
ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;
</code>
WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
SELECT id, name, parent_id, id AS orig_id
FROM tags
WHERE parent_id IS NULL
UNION ALL
SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
FROM tags t1
INNER JOIN cte t2
ON t2.id = t1.parent_id
)
SELECT MAX(p.name) AS name
FROM cte t
LEFT JOIN products p
ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;
Demo
The strategy here is to start the recursive CTE at the top of each product hierarchy, which means the records having no (NULL) parents. Note that we pass down the original parent id value as we recurse. This means that we can figure out which products in the hierarchy belong to the same group.
We then join to the products table, to bring in the name, and we aggregate by the orig_id group. A match is a hierarchy having the desired id, somewhere along the chain.
8