GROUP BY in Laravel Collections

When using Group By at the database level, you must aggregate all the columns. But what if you want to group the data by a column for a more accessible display like this:

You can't do that with a database, but you can with Collections! Let's look at a few valuable methods of collections.

Group By in Collection - Set a Key For Related Items

The groupBy() method on collections often groups data into a different format. And it's as easy as specifying a field:

$questions = Question::query()
    ->with(['topic', 'questionAnswers'])
    ->get()
    // Note that the groupBy is AFTER the database query is executed, meaning that it's a collection method
    ->groupBy('topic.name');

This allowed us to quickly group all of our questions within a topic.name key for a quick display result:

@foreach($questions as $topicName => $topicQuestions)
    <div class="mb-4">
        <h2 class="text-2xl">{{ $topicName }}</h2>
 
        <div>
            @foreach($topicQuestions as $question)
                <p>{{ $question->question }}</p>
                <small>Choices - {{ $question->questionAnswers->count() }}</small>
            @endforeach
        </div>
    </div>
@endforeach

Giving us a result of grouped questions and amounts of choices that each question has:

This function also accepts a callback, where we can define a custom structure:

$questions = Question::query()
    ->with(['topic', 'questionAnswers'])
    ->get()
    ->groupBy(function(Question $question){
        return \Str::of($question->question)
            ->append(' - ')
            ->append($question->questionAnswers->count())
            ->toString();
    });

Which will give us the following:

Key By - Use Any Field as Index

Another helpful method in collections is keyBy(), which allows us to change the default keys of our array to be any other field (it has to be unique!). For example:

$topics = Topic::query()
    ->get()
    ->keyBy('name');

And now, when displaying the list, we can take a foreach and instantly have a name available:

@foreach($topics as $name => $topic)
    <div class="border-b py-4">
        <h2 class="text-2xl">{{ $name }}</h2>
        {{-- Topic details inside --}}
    </div>
@endforeach

It also accepts a callback in which we can define our field or mutate it:

$topics = Topic::query()
    ->with(['questions'])
    ->get()
    ->keyBy(function(Topic $topic){
        return \Str::of($topic->name)
            ->append(' - ')
            ->append($topic->questions->count());
    });

This gives a result where our names are appended with a questions count:

Counting How Many Times Something Repeats - Using Count By

Another great collection feature is countBy(), which can count how many times a specific value occurs in your array. For example, we have an age field on our Users table and want to see how many users we have per age:

$ageList = User::query()
    ->get()
    ->countBy('age')
    // We added a bonus to sort the age in ascending order
    ->sortKeys();

This allows us to display the list like this quickly:

<h2 class="text-2xl">Users count by Age</h2>
 
@foreach($ageList as $age => $count)
    <p>
        {{ $age }} - {{ $count }}
    </p>
@endforeach

And now, we have a count of people by their age:

This is great if you want to get a key in an array of specific values and then count how many times it has repeated in your dataset.

Other Methods - Sum, Avg, Min, Max

In cases when you have the data loaded and need to display a sum of values, average, min, or max, you can use the collection methods sum(), avg(), min(), max() to do so:

We have loaded the orders with their users: (Note: You should not load all the data if you don't need it, but for the sake of example, we will do so)

Controller

$orders = Order::query()
    ->with('user')
    ->get();

Then in our view, before the table of orders, we can display some statistics:

View

<div class="grid grid-cols-4 gap-4">
    <div class="">
        <h2 class="text-xl">Total Orders</h2>
        <span>$ {{ number_format($orders->sum('total'), 2) }}</span>
    </div>
    <div class="">
        <h2 class="text-xl">Average Order Value</h2>
        <span>$ {{ number_format($orders->avg('total'), 2) }}</span>
    </div>
    <div class="">
        <h2 class="text-xl">Minimum Order value</h2>
        <span>$ {{ number_format($orders->min('total'), 2) }}</span>
    </div>
    <div class="">
        <h2 class="text-xl">Maximum Order value</h2>
        <span>$ {{ number_format($orders->max('total'), 2) }}</span>
    </div>
</div>
 
{{-- Table code --}}

This will give us the following result:

With little code, we implemented quick ways to get different statistics from our data.