Здравствуйте. Подскажите пожалуйста как осуществить вот такую задачу.
Есть 3 таблицы в БД
1. modx_shop
id|name|wh_id|active 1 | A | 1 | 1 2 | B | 2 | 1
2. modx_nomenclature_goods
id|name|active 1 | G1 | 1 2 | G2 | 1 3 | G3 | 1 4 | G4 | 1 5 | G5 | 1
3. modx_wh_products
id|wh_id|product_id|count 1 | 1 | 1 | 10 2 | 2 | 2 | 10 3 | 1 | 3 | 10 4 | 2 | 3 | 10 5 | 2 | 5 | 10 6 | 1 | 5 | 10
В результате нужно показать вот такую таблицу
Товар | A | B G1 | 10| 0 G2 | 0 | 10 G3 | 10| 10 G4 | 0 | 0 G5 | 10| 10
Главное условие это то что склады могут добавится/убавится, так же как и номенклатура
Мое решение свелось к тому что я получаю вот такой массив
array( [0] => array( "product" => G1, "data" => array( [0] => array( "shop" => "A", "count" => 10 ) ) ) )
Далее с помощью foreach и еще 1го дополнительного запроса собирается таблица.
На сколько это будет верно, и если есть подсказка на более лучшее решение прошу подсказать
Здесь вся веселость в том, что на выходе получается матрица.Если бы количество складов было четко определенным, все решилось бы одним запросом с несколькими открытыми соединениями (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
Ага так и сделал.
CONCAT("count:'", CONVERT(IFNULL(P.count, 0) USING utf8),"'}")
И как? Зашуршало?
Вообще отлично… особенно устроила скорость
Вот окончательный вариант запроса
$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 вариант…
Спасибо Вам огромное Николай за столь результативную беседу.
Пожалуйста!