Расчет итоговых значений с использованием условных агрегаций

Недавно меня спросили, как наиболее эффективно рассчитать несколько итоговых значений (aggregates) в Laravel. Например,у вас есть служба подписки по электронной почте и вы хотите отображать различные сводные показатели для неё:

Предположим, что у нас есть таблица subscribers в БД с данными в таком формате:

В идеале мы хотели бы рассчитать эти значения, используя всего один запрос к базе данных. Однако, если вы не знаете, как это сделать, то можете выбрать более легкий путь и просто сделать так:

$total = Subscriber::count();
$confirmed = Subscriber::where('status', 'confirmed')->count();
$unconfirmed = Subscriber::where('status', 'unconfirmed')->count();
$cancelled = Subscriber::where('status', 'cancelled')->count();
$bounced = Subscriber::where('status', 'bounced')->count();

Но, конечно, это приведет к пяти запросам к БД, что, как мы знаем, не хорошо. Так что вы можете даже постараться и решить это по-другому:

$subscribers = Subscriber::all();
$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$bounced = $subscribers->where('status', 'bounced')->count();

Здесь мы делаем один запрос к базе данных, чтобы получить всех подписчиков, а затем выполняем подсчет полученной коллекции. Дело в том, что этот подход на самом деле значительно хуже, чем решение с несколькими запросами. Если у нашего приложения тысячи или миллионы подписчиков, время на обработку всех записей будет затрачено очень много и потребует кучу памяти.

Условные агрегации

На самом деле существует очень простой способ подсчитать эти суммы, используя всего один запрос к базе данных. Хитрость заключается в том, чтобы поместить условия в агрегатные функции (aggregate functions). Вот SQL пример:

select
  count(*) as total,
  count(case when status = 'confirmed' then 1 end) as confirmed,
  count(case when status = 'unconfirmed' then 1 end) as unconfirmed,
  count(case when status = 'cancelled' then 1 end) as cancelled,
  count(case when status = 'bounced' then 1 end) as bounced
from subscribers

 total | confirmed | unconfirmed | cancelled | bounced
-------+-----------+-------------+-----------+---------
   200 |       150 |          50 |        30 |      25

Вот как вы можете написать этот запрос в Laravel, используя построитель запросов:

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw("count(case when status = 'confirmed' then 1 end) as confirmed")
    ->selectRaw("count(case when status = 'unconfirmed' then 1 end) as unconfirmed")
    ->selectRaw("count(case when status = 'cancelled' then 1 end) as cancelled")
    ->selectRaw("count(case when status = 'bounced' then 1 end) as bounced")
    ->first();

<div>Total: {{ $totals->total }}</div>
<div>Confirmed: {{ $totals->confirmed }}</div>
<div>Unconfirmed: {{ $totals->unconfirmed }}</div>
<div>Cancelled: {{ $totals->cancelled }}</div>
<div>Bounced: {{ $totals->bounced }}</div>

Довольно круто, ага?

Логические столбцы

Этот подход еще проще, если вы используете boolean столбцы. Хорошим примером использования является суммирование различных ролей в приложении.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

Это работает, так как count игнорирует null столбцы. В отличие от PHP, где false or null возвращает false, в SQL (и JavaScript, кстати говоря) он возвращает null. По сути, A or B возвращает значение A, если A может быть приведено к true; в противном случае возвращается B.

Фильтры

Наконец, если вы используете PostgreSQL, вы также можете использовать filter для достижения наших целей. Они хороши тем, что позволяют вам писать условия, используя обычные операторы where. Кроме того, основываясь на моем тестировании, filter самом деле быстрее, чем выше упомянутые подходы.

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(*) filter (where is_admin) as admins')
    ->selectRaw('count(*) filter (where is_treasurer) as treasurers')
    ->selectRaw('count(*) filter (where is_editor) as editors')
    ->selectRaw('count(*) filter (where is_manager) as managers')
    ->first();

Вот и всё! Обязательно используйте агрегаты в следующий раз, когда создаете панель управления или какой-либо другой тип сводных данных в своем приложении!

Автор: Jonathan Reinink
Перевод: Алексей Широков