Как правильно хранить остатки товаров, когда n складов?

Question

[VictoRD11]

Всем привет!
Нужно помочь з правильным хранениям остатки товаров, когда есть n складов.

Сейчас есть 3 таблицы, хранения товаров (≈ 300 000), храненния остатков (≈ 1 000 000), склады(5):

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=224583 DEFAULT CHARSET=utf8;

CREATE TABLE `stocks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stock` int(11) DEFAULT NULL,
  `product` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`product`)
) ENGINE=InnoDB AUTO_INCREMENT=536813420 DEFAULT CHARSET=utf8;

CREATE TABLE `stock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) CHARACTER SET cp1251 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Чтоб получить список товаров и их остатки, составил вот такой запрос:

SELECT products.id, products.name, GROUP_CONCAT(CONCAT_WS("|", stocks.stock, stocks.count) SEPARATOR ",") AS counts FROM products LEFT JOIN stocks ON products.id = stocks.product GROUP BY products.id LIMIT 0, 10

Результат запроса:


После получения данных склады (counts) разделяю на PHP функцией explode

Теперь вопросы:
1. Есть ли лучшие решения хранить остатки?
2. Как правельно фильтровать? (Например если я хочу по двум складам только учитывать наявные товары)
3. Как можно получить остатки без explode?

Comments

[sim3x]

как учитываешь уход приход по складам?
Будут ли проблемы, если у тебя со склада пропадет товар в неизвестном направлении?

[VictoRD11]

sim3x:
1. 1C вигружает данные
2. Как он может пропасть в неизвестном направлении?

[sim3x]

VictoRD11:
1 те твоя бд копия для сайта?
2 update db_table field_x=0

[VictoRD11]

1. Она обновляеться приблизетельно раз в минуту
2. Не понял

[sim3x]

VictoRD11:
омг
приблизительно

2 Человек, случайно или специально изменит данные в таблице sql-запросом

Как 1с забирает заказ или изменение состояния дел на складе?

[VictoRD11]

Не будет проблем, так как у меня не главная база, она увидит изменния и перезапишет с главной.
Статусы есть

Answer 1

[Вячеслав Плиско]

Почитайте об их Eloquent, пропишите связи и тогда работа будет очень простой.

$products = Products::with(['stocks', 'stocks.stock'])->find($id);
$product->name;
$product->stocks; // массив со стоками из stocks
$product->stocks[0]->name; // выведен данные из таблицы stock

Comments

[VictoRD11]

Eloquent One To Many не поможет, очень много запросов идет, и время виполненния огромное

[Вячеслав Плиско]

VictoRD11: потому я и написал with, будет один дополнительный запрос с IN на связь. Отработать может даже быстрее чем ваш джойн.

[Вячеслав Плиско]

VictoRD11: https://laravel.com/docs/5.3/eloquent-relationship...

[VictoRD11]

Вячеслав Плиско: Понял, сделал но laravel выдает "Mysql Prepared statement contains too many placeholders"

[VictoRD11]

Сейчас попробую chunk сделать

[VictoRD11]

С chunky 145,5 секунд выполняеться запрос :(

[Вячеслав Плиско]

VictoRD11: А сколько вы продуктов за раз тащите, что столько данных получаете? Сколько сторов у одного товара.

[VictoRD11]

Вячеслав Плиско: За раз ≈ 300 000, складов 4

[Вячеслав Плиско]

VictoRD11: тогда 145сек это отличный результат с учётом того что передаётся куа данных, которые ещё и врапятся в объекты. Посмотрите ещё за памятью, чтобы не текло сильно. Если совсем критично, можно использовать DB::select, но это большой костыль, который в последствии может аукнуться.

А зачем вам столько данных тащить за раз?

[VictoRD11]

Вячеслав Плиско: , Как вариант использовать очереди. Сейчас DB::select +1 left join c group_concat 5 секунду выполняеться при условии что ≈ 300 000 товаров, 4 склада

Для прайсов, в формате xlsx. чтобы клиенты могли скачать.

[VictoRD11]

Вячеслав Плиско: Но когда записавую в файл xlsx то примерно ≈ 10 минут :(( Библиотека PHPExcel
Если Storage::disk()->put() -- то за секунди

Answer 2

[lega]

В ваших таблицах socks.id похоже не нужен вообще, трата памяти и индекс.

Это похоже на кеш (второстепенную) таблицу, тогда можно вообще одной таблицей обойтись (или двумя), в каждом продукте хранить список складов с остатками.
Сэкономите на индексах и работать будет быстрее (без джойнов в БД).
Если нужна будет фильтрация по складу тогда можно будет наложить индекс массив складов (если mysql уже это поддерживает).

Comments

[VictoRD11]

1. Согласен
2. Если новый склад добавить тогда что?

[lega]

VictoRD11: 2. Все норм, видите какие-то проблемы?

[VictoRD11]

lega: Новое поле создавать вручную? если например 100 складов, тогда что?

[lega]

VictoRD11: json для этого самое то

[VictoRD11]

lega: хранить в сереализации? нет спасибо)

[lega]

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

[VictoRD11]

lega: mysql 5.7 поддержывает json, ну как вариант так можно, а как where делать?

[lega]

VictoRD11: в mysql можно использовать виртуальные колонки, но похоже массивов он не понимает. В MongoDB и Postgres это работает из коробки.

Answer 3

[Павел]

не проще ли использовать inner join?

SELECT products.id, products.name, stock.name, stocks.count
from products
inner join stocks ON stocks.product = product.id
Inner join stocks ON stocks.stock =stock.id
GROUP BY products.id

дальше через where можете фильтровать как вам надо.

Comments

[VictoRD11]

Не подойдет, так как мне нужно в одном рядке сразу n складов

[Павел]

Так добавьте в Ваш запрос "where stocks.id in "ID складов через запятую"

Answer 4

[Леонид]

Не надо вообще хранить остатки. Остатки еа любую дату и о́бороты за период легко получить полным пересчетом. Для современного железа не проблема