Example 5. GROUP BY with ORDER BY

Another everyday use case is to group by a column and order by another. For example, we want to group user orders and order these entries by the total orders count:

Controller

$orders = Order::selectRaw(
        'users.name as user_name, sum(orders.total) as order_total, sum(order_p.total) as total_products'
    )
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join(
        DB::raw('(select order_id, count(*) as total
             from `order_product`
             group by order_id) as order_p'),
        'order_p.order_id', '=', 'orders.id',
    )
    ->groupBy('user_name')
    ->orderBy('total_products', 'desc')
    ->get();
 
return view('examples.groupByRelatedColumnEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select users.name         as user_name,
       sum(orders.total)  as order_total,
       sum(order_p.total) as total_products
from `orders`
         inner join `users` on `orders`.`user_id` = `users`.`id`
         inner join (select order_id, count(*) as total
                     from `order_product`
                     group by order_id) as order_p on `order_p`.`order_id` = `orders`.`id`
group by `user_name`
order by `total_products` desc

Then in our view, we can display the order_total and total_products columns:

View

{{-- ... --}}
<table class="table-auto w-full">
    <thead>
    <tr>
        <th class="px-4 py-2">User</th>
        <th class="px-4 py-2">Total</th>
        <th class="px-4 py-2">Total Items</th>
    </tr>
    </thead>
    <tbody>
    @foreach ($orders as $order)
        <tr>
            <td class="border px-4 py-2">{{ $order->user_name }}</td>
            <td class="border px-4 py-2">${{ number_format($order->order_total / 100, 2) }}</td>
            <td class="border px-4 py-2">{{ $order->total_products }}</td>
        </tr>
    @endforeach
    </tbody>
</table>
{{-- ... --}}

You can find the Automated test example for this lesson in our repository