Our first practical example will start with a simple query. We will get active and inactive users from the database. For this, we'll use a field active
on our users that have 1
or 0
:
Typically, people would do this with two queries, one for active users and one for inactive users:
$activeUsers = User::where('active', 1)->get();$inactiveUsers = User::where('active', 0)->get();
But we can do this with one query using groupBy
:
$statusCount = User::query() ->addSelect(\DB::raw('count(*) as count')) ->groupBy('active') ->get();
This allows us to get the count of active and inactive users with one query:
select count(*) as countfrom `users`group by `active`
And display it in our view:
<h2 class="text-2xl">Users count by Status</h2> <table class="table-auto w-full"> <thead> <tr> <th class="px-4 py-2">Status</th> <th class="px-4 py-2">Count</th> </tr> </thead> <tbody> <tr> <td class="border px-4 py-2">Active</td> <td class="border px-4 py-2">{{ $statusCount['1']->count }}</td> </tr> <tr> <td class="border px-4 py-2">Inactive</td> <td class="border px-4 py-2">{{ $statusCount['0']->count }}</td> </tr> </tbody></table>
Which will give us the following:
Another way to do this is with a case
statement:
$statusCount = User::selectRaw(" COUNT(CASE WHEN active = '1' THEN 1 END) AS active_users, COUNT(CASE WHEN active = '0' THEN 1 END) AS inactive_users ") ->toBase() ->first();
This will give us the same result as above, but with one query:
select COUNT(CASE WHEN active = '1' THEN 1 END) AS active_users, COUNT(CASE WHEN active = '0' THEN 1 END) AS inactive_usersfrom `users`limit 1
With this, we can display the data in our view:
<table class="table-auto w-full"> <thead> <tr> <th class="px-4 py-2">Status</th> <th class="px-4 py-2">Count</th> </tr> </thead> <tbody> <tr> <td class="border px-4 py-2">Active</td> <td class="border px-4 py-2">{{ $statusCount->active_users }}</td> </tr> <tr> <td class="border px-4 py-2">Inactive</td> <td class="border px-4 py-2">{{ $statusCount->inactive_users }}</td> </tr> </tbody></table>
Both methods are good to use, but in the second one, you must write a query that includes the case
function. It might not be as readable as the first one, but it's good to know that you can also do it this way.
No comments yet…