Как получить все продукты, связанные с цепочкой тегов?
У меня есть две таблицы:
// 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 |
| 3 | Samsung A20 | 7 |
+----+------------------------------------------+--------+
Мне нужно реализовать страницу со списком продуктов для каждого «тега». Итак, мне нужно получить
| 2 | iPhone 14 Promax | 7 |
| 3 | Samsung A20 | 7 |
передав каждый из следующих tag.ids: 5, 6, 7 (поскольку все они относятся к этим продуктам)
Есть идеи, как я могу это сделать?
Упрощенная рабочий пример с реальными данными
Вот мой текущий запрос: (проблема в том, что он возвращает только один продукт, а не список продуктов)
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;
@Dogbert Ожидаемый результат — это список продуктов, связанных с переданным идентификатором тега. Теги представляют собой иерархию, поэтому, если у продукта tag_id = 10 и у этого тега есть родительский элемент, то продукт все равно необходимо выбрать, если переданный тег идентификатор является родителем
в вашем cte начните с выбора тега, для которого предназначен ваш запрос, а затем рекурсивно найдите его дочерние элементы. поэтому, если вы начинаете с тега 5, ваш cte дает 7, 6 и 5. затем просто выполните простое объединение продуктов с тегом cte.
Ответы 1
См. пример
WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
SELECT id, name, parent_id, id AS orig_id
FROM tags
WHERE id=7 -- start tag
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 p.*,t.*
FROM cte t
inner JOIN products p
ON p.tag_id = t.id
рабочий пример
ИЛИ с вашими данными
рабочий пример
Вывод рекурсивного запроса (3 уровня) перед (внутренним) соединением с продуктом
Однако ваши тестовые данные не включают строки продуктов на промежуточных уровнях иерархии. Попробуйте ввести такие данные для проверки.
В вашей части запроса нет необходимости
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;
Достаточно
WHERE id=6 -- start tag
на якоре.
Судя по моему комментарию выше, я подозреваю, что на самом деле у них нет промежуточных уровней, поэтому рекурсия действительно не нужна. Хотя это позволяет добавить несколько уровней позже.
Про количество уровней ничего сказать не могу. В приведенных данных их как минимум 3 (db-fiddle.com/f/p7BRJDSYB3wSA36Yg1TNyS/0 ) или ( dbfiddle.uk/ZD1cUNkp).
Ах да. У них просто нет продуктов на промежуточном уровне, только на листьях.
Да, это. Я думаю, однако, что промежуточные уровни также должны быть проверены ОП.
Каков ожидаемый ввод/вывод данных в скрипте?