Склеить и вывести список каталогов с подсчетов данных

Question

Не получается сформировать правильный запрос, чтобы вывести список каталогов из catalogs с доп. полями:

кол-во товаров в каталоге
кол-во товаров с пустыми image_url <> '' в каталоге
кол-во товаров с нулевым price IS NULL
кол-во товаров со статусом true для mbr_status

Если с кол-вом товаров в каталоге все понятно:

select catalogs.*, 
(SELECT COUNT(*) FROM oproduct_to_catalog WHERE product_to_catalog.catalog_id = catalogs.id) AS product_cnt 
from catalogs

Как быть с подзапросами или тут можно применить EXIST, LEFT JOIN?

Структура:

CREATE TABLE `catalogs` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`)
)

CREATE TABLE `product_to_catalog` (
    `catalog_id` INT(11) NOT NULL,
    `product_id` INT(11) NOT NULL
)

CREATE TABLE `products` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `sku` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `price` DOUBLE(8,2) NOT NULL,
    `image_url` VARCHAR(160) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `mbr_status` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Answer 1

@Akina благодарю за комментарий, было непонятно, почему конструкция с LEFT JOIN не срабатывала и ругалась на группировку.

SELECT `c`.`id`,
       `c`.`name`,
       IFNULL(NULLIF(CAST(count(p.id) AS char), "0"), "") AS cnt_products,
       SUM(p.cnt_outstock) AS cnt_outstock,
       SUM(p.cnt_empty_img) AS cnt_empty_img,
       SUM(p.cnt_null_price) AS cnt_null_price
FROM `catalogs` AS `c`
LEFT JOIN `product_to_catalog` AS `pt` ON `pt`.`catalog_id` = `c`.`id`
LEFT JOIN
  (SELECT *,
          CASE
              WHEN price = "0" THEN 1
              ELSE 0
          END AS cnt_null_price,
          CASE
              WHEN mbr_status = 0 THEN 1
              ELSE 0
          END AS cnt_outstock,
          CASE
              WHEN image_url IS NULL
                   OR image_url = "" THEN 1
              ELSE 0
          END AS cnt_empty_img
   FROM `products`
   GROUP BY `id`) p ON `p`.`id` = `pt`.`product_id`
GROUP BY `c`.`id`

Для Laravel запрос будет выглядеть так:

$sub = DB::table('products')->select('*')
    ->selectRaw('CASE WHEN price = "0" THEN 1 ELSE 0 END AS cnt_null_price')
    ->selectRaw('CASE WHEN mbr_status = 0 THEN 1 ELSE 0 END AS cnt_outstock')
    ->selectRaw('CASE WHEN image_url IS NULL OR image_url = "" THEN 1 ELSE 0 END AS cnt_empty_img')
    ->groupBy('id');

$result = DB::table('catalogs as c')
    ->select('c.id', 'c.name')
    ->selectRaw('IFNULL(NULLIF(CAST(count(p.id) AS char), "0"), "") AS cnt_products')
    ->selectRaw('SUM(p.cnt_outstock) AS cnt_outstock')
    ->selectRaw('SUM(p.cnt_empty_img) AS cnt_empty_img')
    ->selectRaw('SUM(p.cnt_null_price) AS cnt_null_price')
    ->leftJoin('product_to_catalog as pt', function ($join) {
    $join->on('pt.catalog_id', '=', 'c.id');
})
    ->leftJoin(DB::raw("({$sub->toSql() }) p") , function ($join) {
    $join->on('p.id', '=', 'pt.product_id');
})
    ->groupBy('c.id')->get();

БЛОГ НА HUSL

Склеить и вывести список каталогов с подсчетов данных

Ответы (1 шт):