Выборка данных из таблицы БД

Здравствуйте. Подскажите пожалуйста как осуществить вот такую задачу. Есть 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 вариант… Спасибо Вам огромное Николай за столь результативную беседу.

Пожалуйста!