Eloquent Trick: Group By Raw with Boolean Condition

I know the title sounds unclear, so let me start with an example. What would be your Eloquent query if you have birth_date field in DB an want to show how many of your users are adult 18+ years, and how many are still children? Let’s take a look.

Step 1. Simple groupBy

First, let me remind you a typical groupBy usage – for example, if you want to group users by city:

$results = User::select('city', \DB::raw('COUNT(id) as amount'))
    ->groupBy('city')
    ->get();

foreach ($results as $result) {
    echo $result->city . ': ' . $result->amount . '<br />';
}

Step 2. groupBy with Raw Condition

Next, what if you want to group by birth year? It looks like this:

$results = User::select(\DB::raw('YEAR(birth_date) as year, COUNT(id) as amount'))
    ->groupBy(\DB::raw('YEAR(birth_date)'))
    ->get();

So, we’re using DB::raw() for all select statement, also repeating the same condition in groupBy(). Here’s the visual result for my dummy seeded data:

Step 3. groupBy Raw with Boolean Condition

Now we’re getting to the “trick” of the article. How to group by condition of “born before or after 2001 year”? So result would return two rows. Here’s the code:

$results = User::select(\DB::raw('YEAR(birth_date) < 2001 as adult, COUNT(id) as amount'))
    ->groupBy(\DB::raw('YEAR(birth_date) < 2001'))
    ->get();

Looks weird, right? Let me explain. Condition YEAR(birth_date) < 2001 will return one of two values – TRUE or FALSE. In other words, 1 or 0. And that’s exactly what we need to group by – whether the person is adult or not. Here’s the result view in Blade:

<table class="table">
    <thead>
        <tr>
            <th>Adult?</th>
            <th>Amount</th>
        </tr>
    </thead>
    <tbody>
        @foreach ($results as $result)
            <tr>
                <td>{{ $result->adult == 1 ? 'Yes' : 'No' }}</td>
                <td>{{ $result->amount }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

Here’s how it looks visually:

You can use such grouping whenever you have clear condition of TRUE/FALSE, like odd/even numbers somewhere, some number is over/under etc.

Read more about raw queries in Eloquent here in the official documentation.