Example 1. Get Active/Inactive Users with One Query

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 count
from `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_users
from `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.

GROUP BY in Laravel Collections Example 2. Generating a Monthly Report for Employee Work Hours

Example 1. Get Active/Inactive Users with One Query

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples

Laravel GroupBy: Practical Examples

Example 1. Get Active/Inactive Users with One Query

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples