Расчитать себестоимость комбинации из нескольких товаров на sql

Question

У меня большая сложная задача: надо расчитать себестоимость комбо в ресторане. Комбо состоит из товара (таблица goods) и блюда (таблица products). В сущности combo одно комбо связано с товарами и блюдами отношениями many-to-many через, соответственно, таблицы products_combos и goods_combos. Чтобы расчитать себестоимость комбо, надо получить по id комбо все id товаров и все id блюд, входящих в комбо, и сложить их себестоимости - возможно, с учетом того, в каких пропорциях товар и блюда входят в комбо.

То есть пока так: по id комбо берем все id входящих в комбо блюд и товаров и складываем их себестоимости. Запросы для расчета себестоимости товаров и блюд уже написаны.

Вот пример запроса, вычисляющего себестоимость товара:

select i.price_for_one
                    from invoice i,
                                    posinfo pi,
                                    goods g,
                                    stock_item si,
                                    warehouses w
                    where g.id = 10120
                      and si.goods_id = g.id
                      and pi.client_legal_informations_id = 12
                      and w.pos_info_id = pi.id
                      and si.warehouses_id = w.id
                      and i.stock_item_id = si.id
                      and i.archive = false
                      and i.expense = false
                    order by i.id
                    limit 1

Каким образом посчитать себестоимость блюда? В голову приходит только цикл по всем id и вызов функции вычисления себестоимости для каждого айди и суммирование, но sql - декларативный язык без циклов, в нем так не получится.

Как это реализовать?

Answer 1

Самый простой вариант в вашем случае это использовать вложенные запросы. Т.е., например, предположим что таблица goods_combo содержит поля combo_id и goods_id - для связи комбо и входящего в него товара.

Тогда запрос будет

SELECT SUM(
   select i.price_for_one
      from invoice i,
           posinfo pi,
           goods g,
           stock_item si,
           warehouses w
      where g.id = goods_combo.good_id
            and si.goods_id = g.id
            and pi.client_legal_informations_id = 12
            and w.pos_info_id = pi.id
            and si.warehouses_id = w.id
            and i.stock_item_id = si.id
            and i.archive = false
            and i.expense = false
            order by i.id
            limit 1
) FROM goods_combo WHERE comdo_id = 40;

Т.е. считаем себестоимость комбо с id = 40

Для этого выбираем все записи относящиеся к этому комбо, а для всех товаров вызывается подзапрос, который считает цену.

Если убрать условие по конкретному combo_id и сделать вместо этого группировку GROUP BY combo_id, то будет уще список комбо с рассчитаной ценой для каждого.

Такое решение самое быстрое, но не самое эффективное и не самое оптимальное.

Возможно в вашем случае будет удобнее создать view на основе вашего запроса, расчитывающего цену продукта. Т.е. будет view (а по сути таблица) где будет четка связь между id товара и его ценой. А дальше уже это view удобнее и проще использовать в запроса, например для join и т.п.

БЛОГ НА HUSL

Расчитать себестоимость комбинации из нескольких товаров на sql

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