Courses

Laravel GroupBy: Practical Examples

Example 1. GROUP BY instead of DISTINCT

Here's a typical example of a problem. Have you seen something like this?

We will show how to fix this error later, but let's start with more simple examples and get more complex, step by step.


Setup: Our Database

For the examples in this section, we will use a database of user orders and products for all examples. The database structure and data look like this:

With the following data:


Typically, GROUP BY is used with aggregating functions like COUNT() OR SUM() but not necessarily. Let's start with a more simple example.

What if we want to display all different users that had any orders purchased:

To do this we can use the groupBy() method on the Order model:

Controller

use App\Models\Order;
 
$orders = Order::query()
->select('user_id')
->with('user:name,id')
->groupBy('user_id')
->get();
 
return view('examples.groupBy', [
'orders' => $orders
]);

This executes the following Queries:

select `user_id`
from `orders`
group by `user_id`
 
select `name`, `id`
from `users`
where `users`.`id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, ..., 100)

Then in our Blade view, we have a $order->user relationship loaded with the user's name:

View

{{-- ... --}}
<table class="table-auto w-full">
<thead>
<tr>
<th class="px-4 py-2">Name</th>
</tr>
</thead>
<tbody>
@foreach ($orders as $order)
<tr>
<td class="border px-4 py-2">{{ $order->user->name }}</td>
</tr>
@endforeach
</tbody>
</table>
{{-- ... --}}

This resulted in Unique rows for each of our users with any order.

In other words, the result would be identical in this example, using GROUP BY instead of the DISTINCT operator.

avatar

is there a repo associated with this course? would like to have seeders and factories so I can follow along.

👍 2
avatar

This time there are no repositories as the dataset is quite simple if I'm being honest. But we can check if we can add one here :)

avatar

its simple but adding just a factory and seeder file to copy from should be sufficient to run those routines

avatar

https://github.com/LaravelDaily/group-by-demo/

Repo for reference

avatar

awesome thanks @Amit