Count Totals Column or Row in Laravel using Conditional Aggregates

Hello artisan, in this example i will explain how to count Laravel table row or column row in laravel eloquent or db qquery. I will show you many approach to calculate count in Laravel. Sometimes we need count of table row or column row in Laravel, in this example i will show you that.

For the purpose of this article, let's assume that we have a subscribers database table and we have a status field with the value of confirmed unconfirmed cancelled  bounced and we need to count them each status individually . Let's see how to count them in Laravel:

This is first approach and bad approach:

$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 in this laravel select count as aggregate from example we will see the second approach to count table value:

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

Now see another example for PostgreSQL users, if you're using PostgreSQL, you can also use filter clauses to have this same data. Further, based on my testing, filter clauses are really 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();

Read also: How to Use Custom Trait in Laravel Controller

Hope it can help you to learn new things.