Calculating totals in Laravel using conditional aggregates

I was asked recently how to calculate multiple totals (aggregates) in Laravel in the most efficient way. For example, maybe you have an email subscription service and want to display various totals based on your subscribers:

For the purpose of this article, let's say that we have a subscribers database table with data in this format:

Ideally we'd like to calculate these values using a single database query. However, if you're not sure how to do that, you could easily find yourself taking the easy path and just doing this:

$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();

But, of course, this will result in five database queries, which we know isn't great. So you may even try to be clever and solve this in another way:

$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();

Here we're making a single database query to get all the subscribers, and then running counts on the resulting collection. The thing is, this approach is actually significantly worse than the multiple queries solution. If our application has thousands or millions of subscribers, the time to process all the records will be slow, and will use a ton of memory.

Conditional aggregates

There's actually a really simple way to calculate these totals using a single database query. The trick is to put conditions within aggregate functions. Here's an example in 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

Here's how you can write this query in Laravel using the query builder:

$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>

Pretty awesome, right?

Boolean columns

This approach is even easier if you're using boolean columns. A good use-case for this is totaling different roles within an application.

$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();

This works since the count aggregate ignores null columns. Unlike in PHP where false or null returns false, in SQL (and JavaScript for that matter) it returns null. Basically, A or B returns the value A if A can be coerced into true; otherwise, it returns B.

Filter clauses

Finally, if you're using PostgreSQL, you can also use filter clauses to achieve this. Filter clauses are great since they allow you to write your conditions using normal where clauses. Further, based on my testing, filter clauses are actually faster than the approaches mentioned above.

$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();

There you have it! Be sure to take advantage of conditional aggregates next time you're building a dashboard or some other type of summary data in your application!

If you have any questions or feedback on this article, send me (@reinink) a message on Twitter. I'd love to hear from you.