代码之家 › 专栏 › 技术社区 › Martin AJ

如何通过父标签获取产品?

mysql sql

Martin AJ · 技术社区 · 7 月前

我有以下表格:

// 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      |
+----+------------------------------------------+--------+

我需要为每个“标签”实现一个列表页面。因此,我需要通过以下方式获得“iPhone 14促销” tags . id s 5 , 6 , 7 (因为它们都与该产品有关)

你知道通过什么查询,我可以通过标签#6(通信)获得产品#2(iPhone 14促销)吗?

注意,我可以通过简单的连接获得标签#7来获得产品#2:

SELECT p.*
FROM products p
JOIN tags t 
  ON t.id = p.tag_id
WHERE t.id = 7

但我不知道如何通过更高级别的标签ID获取产品。

1 回复 | 直到 7 月前

Tim Biegeleisen 7 月前

在MySQL 8+上,我们可以使用递归CTE:

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;