Блог

Я хочу создать экспорт CSV для заказов, показывающий warehouse_id, откуда был отправлен каждый order_item, если таковой имеется.

Для краткости, вот соответствующая схема:

 create table o (id integer);
  

заказы имеют много order_items:

 create table oi (id integer, o_id integer, sku text, quantity integer);
  

Для каждого элемента order_item в CSV мы хотим показать идентификатор склада, откуда он был отправлен. Но это не сохраняется в order_items . Он хранится в пересылке.

Заказ может быть разделен на множество отправлений с потенциально разных складов.

 create table s (id integer, o_id integer, warehouse_id integer);
  

в отправлениях тоже много отправлений:

 create table si (id integer, s_id integer, oi_id integer, quantity_shipped integer);
  

Как мне извлечь warehouse_id для каждого order_item, учитывая, что warehouse_id находится в отгрузке, и еще не каждый заказ отправлен (может не иметь записи отгрузки или shipment_items).

Мы делаем что-то вроде этого (упрощенный):

 select oi.sku, s.warehouse_id from oi 
left join s on s.o_id = oi.o_id;
  

Однако, если в заказе есть 2 элемента заказа, давайте назовем их артикулами A и B. И этот заказ был разделен на две партии, где A был отправлен со склада ’50’, а затем вторая партия отправила B со склада ‘200’.

То, что мы хотим, было бы выводом CSV, например:

  sku | warehouse_id
-----|--------------
  A  |           50
  B  |          200
  

Но то, что мы получаем, является своего рода декартовым произведением:

=================================

 Here is the sample data:

select * from o;
 id
----
  1
(1 row)

select * from oi;
 id | o_id | sku | quantity
---- ------ ----- ----------
  1 |    1 | A   |        1
  2 |    1 | B   |        1
(2 rows)

select * from s;
 id | o_id | warehouse_id
---- ------ --------------
  1 |    1 |           50
  2 |    1 |          200
(2 rows)

select * from si;
 id | s_id | oi_id
---- ------ ------
  1 |    1 |    1
  2 |    2 |    2
(2 rows)

select oi.sku, s.warehouse_id from oi left join s on s.o_id = oi.o_id;
 sku | warehouse_id
----- --------------
 A   |           50
 A   |          200
 B   |           50
 B   |          200
(4 rows)
  

ОБНОВЛЕНИЕ =========

Согласно Спенсеру, я добавляю другой пример с разными идентификаторами pk для большей ясности. Ниже приведены 2 примера заказов. Заказ 2 содержит элементы A, B, C. A, B отправляются из отгрузки 200, C отправляются из отгрузки 201. Заказ 3 содержит 2 позиции E и A. E еще не отгружен, а A отгружается дважды с одного и того же склада «700» (как это было в обратном заказе).

 # select * from o;
 id
----
  2
  3
(2 rows)

# select * from oi;
 id  | o_id | sku | quantity
----- ------ ----- ----------
 100 |    2 | A   |        1
 101 |    2 | B   |        1
 102 |    2 | C   |        1
 103 |    3 | E   |        1
 104 |    3 | A   |        2
(5 rows)

# select * from s;
 id  | o_id | warehouse_id
----- ------ --------------
 200 |    2 |          700
 201 |    2 |          800
 202 |    3 |          700
 203 |    3 |          700
(4 rows)

# select * from si;
 id  | s_id | oi_id
----- ------ -------
 300 |  200 |   100
 301 |  200 |   101
 302 |  201 |   102
 303 |  202 |   104
 304 |  203 |   104
(5 rows)
  

Я думаю, это работает, я использую left join, чтобы сохранить order_items в отчете, независимо от того, отправлен заказ или нет, я использую group by для сквоша нескольких отправлений с одного склада. Я считаю, что это то, что мне нужно.

 # select oi.o_id, oi.id, oi.sku, s.warehouse_id from oi left join si on si.oi_id = oi.id left join s on s.id = si.s_id group by oi.o_id, oi.id, oi.sku, s.warehouse_id order by oi.o_id;
 o_id | id  | sku | warehouse_id
------ ----- ----- --------------
    2 | 102 | C   |          800
    2 | 101 | B   |          700
    2 | 100 | A   |          700
    3 | 104 | A   |          700
    3 | 103 | E   |
(5 rows)
  

Заказывайте товары, которые были отправлены …

 SELECT oi.id
     , oi.sku
     , s.warehouse_id
  FROM oi
  JOIN si ON si.oi_id = oi.id
  JOIN s  ON s.id     = si.s_id
  

Заказывайте товары, которые еще не отправлены, используя антисоединение, чтобы исключить строки, в которых есть совпадающая строка в si

 SELECT oi.id
     , oi.sku
     , s.warehouse_id
  FROM oi
  JOIN s ON s.o_id = oi.o_id      -- fk to fk shortcut join
    -- anti-join
  LEFT
  JOIN si ON si.oi_id = oi.id
 WHERE si.oi_id IS NULL
  

Но это все равно приведет к (частичному) декартову произведению. Мы можем добавить предложение GROUP BY для свертывания строк…

  GROUP BY si.oi_id
  

Это не позволяет избежать создания промежуточного декартова произведения; добавление предложения GROUP BY сворачивает набор. Но не определено, какая из совпадающих строк из s значений столбцов будет возвращена.

Два запроса могут быть объединены с помощью операции ОБЪЕДИНЕНИЯ ВСЕХ. Если бы я это сделал, я бы, скорее всего, добавил столбец дискриминатора (дополнительный столбец в каждом запросе с разными значениями, который сообщал бы, какой запрос вернул строку.)

Этот набор может соответствовать спецификации, изложенной в вопросе OP. Но я не думаю, что это действительно тот набор, который нужно вернуть. Определение того, с какого склада должен быть отправлен товар, может включать несколько факторов … общее заказанное количество, количество, доступное на каждом складе, может ли заказ быть выполнен с одного склада, какой склад ближе к месту назначения доставки и т.д.

Я не хочу, чтобы у кого-либо создалось впечатление, что этот запрос действительно является «исправлением» проблемы декартова произведения … этот запрос просто скрывает большую проблему.

Я думаю, вам нужна si таблица:

 select oi.sku, s.warehouse_id
from si join
     oi
     on si.o_id = oi.o_id join
     s
     on s.s_id = si.s_id;
  

si кажется, это правильная таблица соединений между таблицами. Я не уверен, почему существует другой ключ соединения, который его не использует.