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

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

Пожалуйста!

FreeCode.Academy

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

Добавить комментарий