Как получить товары по родительским тегам?
У меня есть следующие таблицы:
// 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 Promax» по каждому из следующих tags.id: 5, 6, 7 (поскольку все они связаны с этим продуктом)
Есть идеи, по какому запросу я могу получить продукт № 2 (iPhone 14 Promax), имея тег № 6 (Связь)?
Заметил, что я могу получить продукт №2, имея тег №7 простым объединением:
SELECT p.*
FROM products p
JOIN tags t
ON t.id = p.tag_id
WHERE t.id = 7
Но я не знаю, как получить продукт по идентификаторам тегов более высокого уровня.
@TimBiegeleisen MySQL версия 8.0.37
Ответы 1
В 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;
Стратегия здесь состоит в том, чтобы запустить рекурсивный CTE наверху каждой иерархии продуктов, что означает, что записи не имеют родительских элементов (NULL). Обратите внимание, что мы передаем исходное родительское значение id при рекурсии. Это означает, что мы можем выяснить, какие продукты в иерархии относятся к одной и той же группе.
Затем мы присоединяемся к таблице продуктов, чтобы ввести название, и объединяем их по группе orig_id. Совпадение — это иерархия, в которой желаемое id находится где-то в цепочке.
Не работает и возвращается null. Хотя когда я заменяю 6 на 7, он также возвращает «iPhone 14 Promax».
Я несколько раз редактировал свой ответ. Можете ли вы перезагрузить страницу и убедиться, что вы используете последнюю версию?
Знаете, логика правильная, знает, что нужно выбрать один товар, но возвращает NULL вместо названия товара («iPhone 14 Promax»)
Я имею в виду, что когда я ставлю 60000 вместо 6, он ничего не возвращает (ни результата, ни строки), но теперь он возвращает одну строку (как и ожидалось), но NULL значение для имени
Проверьте редактирование, я исправил.
Ваш запрос великолепен, но есть небольшая проблема, он всегда возвращает одну строку. Я добавил новый продукт «Samsung Galaxy A55», и ваш запрос переключился на новый определенный продукт и проигнорировал «iPhone 14 Promax», хотя они оба должны быть выбраны. dbfiddle.uk/zn353A_W
Если вы можете смириться с получением CSV-вывода всех соответствующих продуктов, просто выберите GROUP_CONCAT(p.name) в последнем запросе, q.v. обновленное демо здесь: dbfiddle.uk/2x7ebC04
Какую версию MySQL вы используете?