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();
Hope it can help you to learn new things.
#laravel #laravel-8x