Calculating totals in Laravel using conditional aggregates

Last updated 78 days ago by Jonathan Reinink

php

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:

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

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

```

Pretty awesome, right?

Read full Article