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>
{{-- ... --}}