MySQL SUM множество к множеству

20 марта 2021, 00:50

=) Есть у меня четыре таблицы: stocks, items, categories, taxonomy Это как все поняли - склад. Нужно вывести категории с подсчетом количества позиций в каждой категории. Вот пример таблиц: https://www.db-fiddle.com/f/vQTgED7YfXWSRti1zt2Y5i/21

Я смог только вывести категории:

SELECT `c`.`name`,
`i`.`count`
FROM `categories` AS `c` 
JOIN `taxonomy` AS `t` ON `c`.`id` = `t`.`term`
JOIN `stocks` AS `st` ON `st`.`id` = `t`.`object`
JOIN `items` AS `i` ON `i`.`id` = `t`.`object`
WHERE (`st`.`id` = 1);

Таблицы:

stocks - это склады
Items - Позиции на складе с текущим количеством
Categories - Категории позиций на складе
Taxonomy - Объединяющая таблица. По задумке она должна объединять склад с позицией и позицию с категорией.

Taxonomy связывает разные таблицы:

если type='category', то это связка склада(term) с категорией(object)
если type='item', то это связка категории(term) с позицией(object)

Суть задачи такова, что нужно вывести список с категориями (что я и сделал) и вторым значением (столбцом) нужно вывести количество позиций в каждой из категорий используя значение count из таблицы items

Answer 1

Проблема в том, что в таблице taxonomy у вас ссылки на разные сущности.
Имея в виду что-то одно, нужно добавлять соответствующий фильтр по полю type.

В первом приближении это выглядит так:

SELECT st.name
  , cat.name category
  , i.name item, i.count
FROM stocks st
JOIN taxonomy t_st ON t_st.type='item' AND t_st.object=st.id
JOIN items i ON i.id = t_st.term
JOIN taxonomy t_cat ON t_cat.type='category' AND t_cat.object=i.id
JOIN categories cat ON cat.id = t_cat.term

К этому уже можно добавить требуемые фильтры/группировки:

SELECT st.name
  , cat.name category
  , GROUP_CONCAT(i.name) items
  , SUM(i.count) cnt
FROM stocks st
JOIN taxonomy t_st ON t_st.type='item' AND t_st.object=st.id
JOIN items i ON i.id = t_st.term
JOIN taxonomy t_cat ON t_cat.type='category' AND t_cat.object=i.id
JOIN categories cat ON cat.id = t_cat.term
WHERE st.id = 1
GROUP BY st.id, cat.id, st.name, cat.name

Я бы всё-таки рекомендовал бы разделить taxonomy на несколько таблиц и добавить соответствующие ограничения внешнего ключа(foreign keys)