As an example, what would be your Eloquent query if you have “birth_date” field in DB want to show how many of the users are adult 18+ years and how many are still children?

STEP 1:- Simple groupBy

The groupBy usage is used for example, if you want to group users by city.

The code:

$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?

The code:

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

So, we are using DB::raw() for all select statement and also repeating the same condition in groupBy(). The result will be like this:

STEP 3:- groupBy Raw with Boolean Condition

How to group by condition of “born before or after 2001 year”? So result would return two rows.

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

Condition YEAR(birth_date) < 2001 will return one of two values which are “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.

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>

The output will be like this:

Credit to: https://bit.ly/3y5cbB7

Leave a Reply

Your email address will not be published. Required fields are marked *