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.