MYSQL Как вычислить иерархию + 1 ко многим в одном запросе?

Question

Помогите понять...
Замучался я с этой иерархией, узнал, что это делается через WITH RECURSIVE, но как реализовать ещё и 1 ко многим тут же?
3адание такое:
Получите все товары из категории «Напитки» и всех её подкатегорий.
Выведите id, имя и стоимость товара, а также название категории.
Название категории выведите в колонке category.
Данные отсортируйте по названию категории, а затем по имени товара.
Что я делал:

  with recursive cte (id, name, price) as (  
  select     id,  
             name,  
             parent_id  
  from       categories  
  where      categories.name = "Напитки"  
  union all  
  select     p.id,  
             p.name,  
             p.price  
  from       products p  
  INNER join cte  
          on p.category_id = cte.id  
)  
select * from cte;

Исходные данные:

На выходе я получил:

А мне нужна и категория, и подкатегории, и товары с их айди и ценами.

Answer 1

WITH RECURSIVE cte AS (
  SELECT *
  FROM categories
  WHERE name = 'Напитки'
  UNION ALL
  SELECT categories.*
  FROM categories
  JOIN cte ON categories.parent_id = cte.id
  )
SELECT products.id, products.name product_name, products.price, cte.name category_name
FROM cte
JOIN products ON cte.id = products.category_id;

id	product_name	price	category_name
3	Сок яблочный	112	Напитки
6	Забористый	109	Квас
8	Французское, 1 литр	89	Вино
10	CocaCola, 1 литр	78	Газировка
11	Fanta, 2 литра	99	Газировка

fiddle с некоторыми пояснениями.

PS. В списке (под)категорий нет вложенности. Если её отсутствие гарантировано (хотя я не знаю, как это можно достичь средствами MySQL, даже триггеры не помогут, ибо всегда есть вариант ошибки и даже злонамеренного действия), то можно обойтись и без рекурсии, просто соединением двух копий таблицы категорий. Думаю, посмотрев на рекурсивный CTE, вы это сможете и самостоятельно...

PPS. Для понимания необходимости рекурсии измените запись (9, 5, 'Квас') на (9, 16, 'Квас').

БЛОГ НА HUSL

MYSQL Как вычислить иерархию + 1 ко многим в одном запросе?

Ответы (1 шт):