Laravel eloquent group eloquent records in hourinterval with Left join

user_11920674 0
Viewed 53

I'm using laravel v8 and i have two tables with structure like this:

orders

id total created_at
1 100 2022-01-01 11:00:05
2 300 2022-01-01 15:00:05
.. .. ...

order_items

id order_id product_id quantity
1 1 2 1
2 1 4 3
.. .. .. ..

i want to generate a report which consist of the summary of order in every hour (59 minutes 59 seconds) interval in one day, for example like this:

Date Time Total Order Total Order Item Quantity Total Order Amount
Jan 01 2020 00:00 - 00:59 0 0 0
Jan 01 2020 01:00 - 01:59 0 0 0
Jan 01 2020 02:00 - 02:59 0 0 0
Jan 01 2020 03:00 - 03:59 1 2 80
Jan 01 2020 04:00 - 04:59 3 10 500
Jan 01 2020 05:00 - 0:59 0 0 0
............ .............. ... ... ...
Jan 01 2020 21:00 - 21:59 5 18 1020
Jan 01 2020 22:00 - 22:59 1 5 210
Jan 01 2020 23:00 - 0:59 0 0 0

Currently i need to select every interval like this and then doing foreach loop to get summarize the data for the interval:

$orders = DB::table('orders')
            ->select(
                [
                    DB::RAW('orders.total as total'),
                    DB::RAW('order_items.quantity as quantity')
                ]
            )
            ->leftJoin ('order_items. AS order_items.', function ($query) {
                $query->on('order_items..order_id', '=', 'orders.id'); 
            })
            ->whereBetween('orders.created_at',[date('Y-m-d 00:00:00'),date('Y-m-d 00:59:59')]) 
             ->get();

$totalOrder = $orders->count();
$totalQuantity = 0;
$totalAmount = 0;
foreach($orders as $order){
   $totalQuantity += $order->quantity;
   $totalAmount += $order->total;
}

$data['00:00 - 00:59'] = [
  'total_order' => $totalOrder,
  'total_quantity' => $totalQuantity,
  'total_amount' => $totalAmount,
];

this solution is working but it's not very effecient , how can i run a single query database to achieve this kind of result ?

laravel date mysql php
1 Answers

Use an alias and group by:

$orders = DB::table('orders')
    ->select(
        [
            DB::RAW('left(orders.created_at,13) as hour'),
            DB::RAW('sum(orders.total) as total'),
            DB::RAW('sum(order_items.quantity) as quantity')
        ]
    )
    ->leftJoin('order_items', 'order_items.order_id', '=', 'orders.id')
    ->groupBy('hour')
    ->get();
user_66327440
Related