Eloquent - Calculating totals using conditional aggregates

Something which looks like this:

If we add the totals, the appearance would change to something like this:

But what's the most efficient way of calculating the totals?

$statuses->requested = Feature::where('status', 'requested')->count();
$statuses->planned = Feature::where('status', 'planned')->count();
$statuses->completed = Feature::where('status', 'completed')->count();

This approach is simple enough and for this example, it's not a big deal as we've only got 3 statuses, but what if we had 5, or 10, or 20? That's when this approach starts to become a problem!

It is possible to fetch everything you need in one database query by writing count aggregate queries. The query would look like this:

SELECT 
count(CASE WHEN status = 'requested' THEN 1 END) AS requested,
count(CASE WHEN status = 'planned' THEN 1 END) AS planned,
count(CASE WHEN status = 'completed' THEN 1 END) AS completed
from features;

So how do we do this with Eloquent?

$statuses = Feature::toBase()
    ->selectRaw("count(CASE WHEN status = 'requested' THEN 1 END) AS requested")
    ->selectRaw("count(CASE WHEN status = 'planned' THEN 1 END) AS planned")
    ->selectRaw("count(CASE WHEN status = 'completed' THEN 1 END) AS completed")
    ->first();

We've now executed one query (so we've got a scalable solution / no N+1 problem), not three and this query is even quicker than the first one ran above.