Posted on May 23, 2019

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:

200 total subscribers 150 confirmed 50 unconfirmed 30 cancelled 25 bounced

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

name email status Adam Campbell adam@hotmeteor.com confirmed Taylor Otwell taylor@laravel.com unconfirmed Jonathan Reinink jonathan@reinink.ca cancelled Adam Wathan adam.wathan@gmail.com bounced

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.

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?

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 .

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.