Вообще отлично… особенно устроила скорость Вот ок
Вообще отлично… особенно устроила скорость
Вот окончательный вариант запроса $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');