Здравствуйте. Подскажите пожалуйста как осуществить вот такую задачу. Есть 3 таблицы в БД
Здесь вся веселость в том, что на выходе получается матрица.Если бы количество складов было четко определенным, все решилось бы одним запросом с несколькими открытыми соединениями (LEFT JOIN, RIGHT JOIN). Здесь же, поскольку количество складов не определено, остается только руками формировать массив.
Сам запрос SELECT N.id, N.name,
CONCAT(
"[",
GROUP_CONCAT(
CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
CONCAT("count:'",CONVERT(P.count USING utf8),"'}")
)
,"]"
) as data
FROM modx_products_nomenclature
N
LEFT JOIN modx_shops
S ON S.active = 1
LEFT JOIN modx_warehouse_products
P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id возвращает вот такое id 1
name G1
data [{shop:'A', count:'10'}]
.....
id 3
name G1
data [{shop:'A', count:'10'}, {shop:'B', count:'10'}] хотелось бы конечно что бы в дата в случае если количество 0 то писало {shop: 'B', count: '0'}
А вот так не получается?: SELECT N.id, N.name,
CONCAT(
"[",
GROUP_CONCAT(
CONCAT("{shop:'",CONVERT(S.name USING utf8),"',"),
CONCAT("count:'", if(P.count > 0, CONVERT(P.count USING utf8), 0),"'}")
)
,"]"
) as data
FROM modx_products_nomenclature
N
LEFT JOIN modx_shops
S ON S.active = 1
LEFT JOIN modx_warehouse_products
P ON P.product_id = N.id and S.warehouse_id = P.warehouse_id
GROUP BY N.id
И как? Зашуршало?
Вообще отлично… особенно устроила скорость
Вот окончательный вариант запроса $q = $modx->newQuery('ProductsNomenclature');
$q->select(
array(
"ProductsNomenclature
.name
as product",
"ProductsNomenclature
.code
as code",
"ProductsGroup
.name
as groupname",
"CONCAT('[',
GROUP_CONCAT(
CONCAT('{"shop":"',Shop
.name
,'",'),
CONCAT('"count":', IFNULL(WarehouseProducts
.count
, 0),','),
CONCAT('"unit":"', ProductsNomenclature
.unit_measure
,'"}')
)
,']') as data",
)
);
$q->innerJoin('Shops', 'Shop', 'Shop
.active
= 1');
$q->innerJoin('ProductsGroup', 'ProductsGroup', 'ProductsGroup
.id
= ProductsNomenclature
.group_id
');
$q->leftJoin('WarehouseProducts', 'WarehouseProducts', 'Shop
.warehouse_id
= WarehouseProducts
.warehouse_id
and ProductsNomenclature
.id
= WarehouseProducts
.product_id
');
$q->where(
array(
'active' => 1,
)
);
$q->sortby('ProductsNomenclature
.group_id
', 'ASC');
$q->groupby('ProductsNomenclature
.id
');
Ну и здорово!
Снова здравствуйте. Вернемся к этой выборке. В ходе работы нашлась какая то злостная «бажина» )) Проблема вот в чем… Если последним в строке значения стоит товар которого нет на складе то запрос валится. вот пример: // ошибка [6] => Array ( [product] => ХХХ [code] => ХХХ [groupname] => ХХХ [data] => [{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":50000,"unit":"шт."},{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":] ) //а вот тут все хорошо [9] => Array ( [product] => ХХХ [code] => ХХХ [groupname] => ХХХ [data] => [{"shop":"Магазин3","count":50000,"unit":"шт."},{"shop":"Магазин4","count":50000,"unit":"шт."},{"shop":"Магазин5","count":50000,"unit":"шт."},{"shop":"Магазин6","count":50000,"unit":"шт."},{"shop":"Магазин7","count":0,"unit":"шт."},{"shop":"Магазин1","count":0,"unit":"шт."},{"shop":"Магазин2","count":5000] ) последовательность магазинов меняется это видно с примеров. подскажите в чем тут косяк?
Судя по всему именно так.
а как решить? есть как бы параметр max_allowed_packet это в нем проблема? или на стандартном хостинге ничего поделать нельзя?
А при чем тут это, когда 99% проблема в типе данных колонки data. Какой тип данных там указан и какая длина?
А, ну да, у вас же там конкатинация. Значит гуглите смену длины для таких операций. Вот один из ответов. Выполняйте запрос SET SESSION group_concat_max_len = 100000; Но это путь в никуда ИМХО.
( понятно что в никуда… сегодня магазинов 7 а завтра 27… и все приехали… а как можно заменить такую выборку? посоветуйте пожалуйста
Вообще как вариант просто взять себе облачный сервер и настроить на нем мускул под свои нужды, включая увеличение длины строки для concat-функций. Но можно просто в цикле для всех магазинов наджоинить таблицу с формированием полей по маске, чтобы на выходе обработать полученные данные и набить в конечный запрос. А можно и вовсе составить запрос так, чтобы на каждый продукт получилось N-число записей, в каждой из которых будет ID магазина, кол-во и т.п. Типа так: select product_id, shop_id, count(*) as total from products p inner join shop_products sp on sp.product_id = p.id inner join shops s on sp.shop_id = s group by product_id, shop_id И полученные данные уже обрабатываете в цикле, набивая конечный массив, так же как у нас TV-шки набиваются.
а ничего что при последнем варианте будет так много обращений к БД? товаров может быть многооо
А где вы увидели много запросов к БД? И, вам уже выбирать какой вариант больше нравится.
ага все я понял как работает 3 вариант… Спасибо Вам огромное Николай за столь результативную беседу.
Пожалуйста!