Example 6. GROUP BY Raw Expression

Sometimes more than a simple column is needed. For example, we want to group our orders by the month they were created. To do this, we will use a raw expression:

Controller

$orders = Order::selectRaw(
    'month(orders.order_time) as month, sum(order_p.quantity) as total_quantity, sum(orders.total) as order_total, count(distinct id) as total_orders'
)
    ->join(
        DB::raw('(select order_id, sum(quantity) as quantity
             from `order_product`
             group by order_id) as order_p'),
        'order_p.order_id', '=', 'orders.id',
    )
    // We are using the month() mysql function to get the month from the order_time column
    ->groupByRaw('month(orders.order_time)')
    ->orderBy('month')
    ->orderBy('total_orders', 'desc')
    ->get();
 
return view('examples.groupByRawMonthWithEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select month(orders.order_time) as month,
       sum(order_p.quantity)    as total_quantity,
       sum(orders.total)        as order_total,
       count(distinct id)       as total_orders
from `orders`
         inner join (select order_id, sum(quantity) as quantity
                     from `order_product`
                     group by order_id) as order_p on `order_p`.`order_id` = `orders`.`id`
group by month(orders.order_time)
order by `month` asc, `total_orders` desc

Then in our view, we can display the columns needed, and they will be grouped by the month:

View

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

This can be used for any raw expression, not just the month. For example, we can group by the day:

Controller

$orders = Order::selectRaw(
    'day(orders.order_time) as day, sum(order_p.quantity) as total_quantity, sum(orders.total) as order_total, count(distinct id) as total_orders'
)
    ->join(
        DB::raw('(select order_id, sum(quantity) as quantity
             from `order_product`
             group by order_id) as order_p'),
        'order_p.order_id', '=', 'orders.id',
    )
    ->groupByRaw('day(orders.order_time)')
    ->orderBy('day')
    ->orderBy('total_orders', 'desc')
    ->get();
 
return view('examples.groupByRawDayWithEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select day(orders.order_time) as day,
       sum(order_p.quantity)  as total_quantity,
       sum(orders.total)      as order_total,
       count(distinct id)     as total_orders
from `orders`
         inner join (select order_id, sum(quantity) as quantity
                     from `order_product`
                     group by order_id) as order_p on `order_p`.`order_id` = `orders`.`id`
group by day(orders.order_time)
order by `day` asc, `total_orders` desc

You can find the Automated test example for this lesson in our repository or Month Test here and Day Test here.