magr0s 12 марта 2015 0 19
Здравствуйте. Подскажите пожалуйста как осуществить вот такую задачу.

Есть 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го дополнительного запроса собирается таблица.

На сколько это будет верно, и если есть подсказка на более лучшее решение прошу подсказать
19 комментариев
Tramp13571
Tramp1357 12 марта 2015г в 10:54 #
Здесь вся веселость в том, что на выходе получается матрица.Если бы количество складов было четко определенным, все решилось бы одним запросом с несколькими открытыми соединениями (LEFT JOIN, RIGHT JOIN).
Здесь же, поскольку количество складов не определено, остается только руками формировать массив.
m
magr0s 12 марта 2015г в 12:17 #
Сам запрос

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'}
Fi1osof1
Fi1osof 13 марта 2015г в 01:10 #
А вот так не получается?:
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
m
magr0s 13 марта 2015г в 12:20 #
Ага так и сделал.

CONCAT("count:'", CONVERT(IFNULL(P.count, 0) USING utf8),"'}")
Fi1osof1
Fi1osof 13 марта 2015г в 19:25 #
И как? Зашуршало?
m
magr0s 16 марта 2015г в 11:16 #
Вообще отлично… особенно устроила скорость
Вот окончательный вариант запроса

$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`');
Fi1osof1
Fi1osof 16 марта 2015г в 12:03 #
Ну и здорово!
m
magr0s 26 марта 2015г в 23:14 #
Снова здравствуйте. Вернемся к этой выборке.
В ходе работы нашлась какая то злостная «бажина» ))

Проблема вот в чем… Если последним в строке значения стоит товар которого нет на складе то запрос валится.

вот пример:


// ошибка
[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]
        )

последовательность магазинов меняется это видно с примеров.

подскажите в чем тут косяк?
m
magr0s 26 марта 2015г в 23:48 #
походу проблема в количестве символов. строка тупо обрезается.
Fi1osof1
Fi1osof 27 марта 2015г в 00:02 #
Судя по всему именно так.
m
magr0s 27 марта 2015г в 00:03 #
а как решить?
есть как бы параметр max_allowed_packet это в нем проблема?

или на стандартном хостинге ничего поделать нельзя?
Fi1osof1
Fi1osof 27 марта 2015г в 00:16 #
А при чем тут это, когда 99% проблема в типе данных колонки data. Какой тип данных там указан и какая длина?
m
magr0s 27 марта 2015г в 00:20 #
нету такой колонки вообще ни в одной таблице… это собирается с нескольких значений
Fi1osof1
Fi1osof 27 марта 2015г в 00:30 #
А, ну да, у вас же там конкатинация. Значит гуглите смену длины для таких операций. Вот один из ответов.
Выполняйте запрос
SET SESSION group_concat_max_len = 100000;
Но это путь в никуда ИМХО.
m
magr0s 27 марта 2015г в 00:37 #
( понятно что в никуда… сегодня магазинов 7 а завтра 27… и все приехали…
а как можно заменить такую выборку? посоветуйте пожалуйста
Fi1osof1
Fi1osof 27 марта 2015г в 00:51 #
Вообще как вариант просто взять себе облачный сервер и настроить на нем мускул под свои нужды, включая увеличение длины строки для 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-шки набиваются.
m
magr0s 27 марта 2015г в 00:54 #
а ничего что при последнем варианте будет так много обращений к БД? товаров может быть многооо
Fi1osof1
Fi1osof 27 марта 2015г в 01:09 #
А где вы увидели много запросов к БД?

И, вам уже выбирать какой вариант больше нравится.
m
magr0s 27 марта 2015г в 01:22 #
ага все я понял как работает 3 вариант…
Спасибо Вам огромное Николай за столь результативную беседу.
Fi1osof1
Fi1osof 27 марта 2015г в 01:26 #
Пожалуйста!
Авторизуйтесь или зарегистрируйтесь (можно через соцсети ), чтобы оставлять комментарии.