Подсчет строк в таблицах (MySQL функция COUNT) для Yii-2

Подсчет количества строк (использование функции MySQL - COUNT) в фреймворке Yii-2 имеет свои особенности. В данной заметке несколько примеров, на базе которых можно строить свои запросы.
Используются 2 таблицы: shop_categories и shop_products (которая имеет поле для связи "category_id").

Подсчет кол-ва категорий.

для MySQL:

SELECT COUNT(*) FROM shop_categories;

для Yii-2:

Category::find()->count();

результат - число, например "11"
или так:

Category::find()->select('COUNT(*) AS cnt')->asArray()->one();

результат в массиве


Важно! Кол-во товаров сохраняется в создаваемое динамически свойство cnt, но в сущности Category его изначально нет. Поэтому, чтобы оно появилось в результатах выборки, его нужно добавить:

class Category extends ActiveRecord
{
    public $cnt;
…

Теперь в данное свойство можно добавлять различные значения при использовании COUNT в запросах.

Подсчет кол-ва товаров для каждого из статусов.

Например товар имеет статусы active - 1 и disabled - 0.
для MySQL:

SELECT status, COUNT(*) AS cnt FROM shop_products WHERE quantity > 0 GROUP BY status

для Yii-2:

Product::find()
    ->select(['status', 'COUNT(*) AS cnt'])
    ->where(['>', 'quantity', 0])
    ->groupBy('status')
    ->asArray()
    ->all();

Подсчет строк в связанной таблице.

Задача отобрать все категории с указанием количества товаров в каждой из них (связь shop_categories.id = shop_products.category_id)
для MYSQL:

SELECT shop_categories.*, COUNT(shop_products.id) cnt
FROM shop_categories
LEFT JOIN shop_products ON shop_categories.id = shop_products.category_id
WHERE shop_categories.depth > 0
GROUP BY id
ORDER BY shop_categories.lft

для Yii-2:
1. вариант (правильный) - тут используется метод joinWith для связи с таблицей products

// Связывающий метод в сущности Category
public function getProduct(): ActiveQuery
{
    return $this->hasOne(Product::class, ['category_id' => 'id']);
}

Пример

$categories = Category::find()->alias('c')
    ->select(['c.*', 'COUNT(p.id) AS cnt'])
    ->joinWith('product p', false)
    ->where(['>', 'c.depth', 0])
    ->groupBy('c.id')
    ->orderBy('c.lft')
    ->all();

Тут я для joinWith вторым аргументом передаю false - отключаю жадную загрузку данной таблицы (products), т.к. она не нужна, а только кол-во товаров из нее.

2. вариант - без использования joinWith, аналогично как на чистом MYSQL:

$categories = Category::find()->alias('c')
    ->select(['c.*', 'COUNT(p.id) AS cnt'])
    ->leftJoin('shop_products p', 'c.id = p.category_id')
    ->where(['>', 'c.depth', 0])
    ->groupBy('c.id')
    ->orderBy('c.lft')
    ->all();

Подсчет строк из нескольких таблиц, суммирование результатов.

Используются 3 таблицы: shop_categories, shop_category_assignments и shop_products
Нужно подсчитать кол-во разных товаров в каждой из категорий. При этом есть 2 связи для таблицы категорий:
1. shop_products имеет прямую связь по полю "category_id";
2. shop_products имеет связь через промежуточную таблицу shop_category_assignments
В category_id указывается только одна главная категория товара, а в таблице shop_category_assignments указываются дополнительные категории.

Т.е. нужно подсчитать сколько разных товаров для каждой категории хранится в таблице shop_products и аналогично в таблице shop_category_assignments, потом эти 2 значения сложить и значение сохранить в свойстве "cnt". Параллельно нужно получить сами категории со всеми полями данной таблицы (shop_categories).

для MySQL:

SELECT shop_categories.*, 
(SELECT COUNT(*) FROM shop_products WHERE shop_categories.id = shop_products.category_id) +
(SELECT COUNT(*) FROM shop_category_assignments WHERE shop_categories.id = shop_category_assignments.category_id) as cnt
FROM shop_categories
WHERE shop_categories.depth > 0
GROUP BY id
ORDER BY shop_categories.lft

Для Yii2:

• используя Query Builder (получаем массивы)

$expression = "(SELECT COUNT(*) FROM shop_products AS p WHERE c.id = p.category_id) +
(SELECT COUNT(*) FROM shop_category_assignments AS ca WHERE c.id = ca.category_id) as cnt";

$categories = (new Query())
    ->select('c.*')
    ->addSelect($expression)
    ->from(['c' => 'shop_categories']) //алиас "c"
    ->where(['>', 'c.depth', 0])
    ->groupBy('c.id')
    ->orderBy('c.lft')
    ->all();

• используя Active Record (получаем объекты)

$expression = "(SELECT COUNT(*) FROM shop_products AS p WHERE c.id = p.category_id) +
(SELECT COUNT(*) FROM shop_category_assignments AS ca WHERE c.id = ca.category_id) as cnt";

$categories = Category::find()->alias('c')
    ->select('c.*')
    ->addSelect($expression)
    ->where(['>', 'c.depth', 0])
    ->groupBy('c.id')
    ->orderBy('c.lft')
    ->all();

Т.к. свойства cnt, в которое будет сохраняться подсчитанное кол-во товаров у сущности (модели) Category нет, нужно предварительно создать такое публичное свойство для данного класса.