Вообще отлично… особенно устроила скорость Вот ок

Вообще отлично… особенно устроила скорость Вот окончательный вариант запроса

$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`');