es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Laravel eloquent agrupa registros eloquent en intervalos de una hora con Left join.

04 Aug, 2023 Programación

Estoy utilizando Laravel v8 y tengo dos tablas con la siguiente estructura:

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

Deseo generar un informe que consista en un resumen de los pedidos en cada intervalo de una hora (59 minutos y 59 segundos) durante un día, por ejemplo, como esto:

Fecha Hora Total de pedidos Cantidad total de productos en pedidos Monto total de pedidos
01 de enero de 2020 00:00 – 00:59 0 0 0
01 de enero de 2020 01:00 – 01:59 0 0 0
01 de enero de 2020 02:00 – 02:59 0 0 0
01 de enero de 2020 03:00 – 03:59 1 2 80
01 de enero de 2020 04:00 – 04:59 3 10 500
01 de enero de 2020 05:00 – 05:59 0 0 0
………… ………….. … … …
01 de enero de 2020 21:00 – 21:59 5 18 1020
01 de enero de 2020 22:00 – 22:59 1 5 210
01 de enero de 2020 23:00 – 23:59 0 0 0

Actualmente tengo que seleccionar cada intervalo de esta forma y luego hacer un bucle foreach para resumir los datos para cada intervalo:

$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,
];

Esta solución funciona, pero no es muy eficiente. ¿Cómo puedo ejecutar una sola consulta en la base de datos para lograr este tipo de resultado?

Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para generar el informe con una sola consulta, puedes usar el ORM Eloquent de Laravel y las funciones GROUP BY y DATE_FORMAT en tu consulta. Aquí tienes un ejemplo de consulta que debería darte los datos del informe que necesitas:

    $reportData = DB::table('orders')
    ->select(
        DB::raw("DATE_FORMAT(created_at, '%b %d %Y') AS fecha"),
        DB::raw("CONCAT(DATE_FORMAT(created_at, '%H'), ':00 - ', DATE_FORMAT(DATE_ADD(created_at, INTERVAL 59 MINUTE), '%H'), ':59') AS intervalo_tiempo"),
        DB::raw("COUNT(DISTINCT orders.id) AS total_ordenes"),
        DB::raw("SUM(IFNULL(order_items.quantity, 0)) AS total_cantidad"),
        DB::raw("SUM(orders.total) AS total_monto")
    )
    ->leftJoin('order_items', 'orders.id', '=', 'order_items.order_id')
    ->whereDate('orders.created_at', '=', '2022-01-01')
    ->groupBy('fecha', DB::raw('FLOOR(TIME_TO_SEC(created_at) / 3600)'))
    ->orderBy('fecha')
    ->orderBy('intervalo_tiempo')
    ->get();

    En esta consulta, primero seleccionamos la fecha y el intervalo de tiempo basado en el campo created_at usando las funciones DATE_FORMAT y CONCAT. También usamos las funciones COUNT y SUM para obtener el total de órdenes, la cantidad total de artículos de la orden y el monto total de la orden para cada intervalo de tiempo. Hacemos un left join con la tabla order_items para incluir la cantidad de artículos de la orden en el resumen.

    Luego usamos la función whereDate para filtrar las órdenes por la fecha para la cual quieres generar el informe, y agrupamos los resultados por fecha e intervalo de hora usando las funciones GROUP BY y FLOOR. Finalmente, ordenamos los resultados por fecha e intervalo de tiempo usando la función orderBy.

    Esta consulta devolverá una colección de objetos que contienen los datos del informe para cada intervalo de tiempo en la fecha dada. Puedes recorrer esta colección para mostrar el informe en el formato deseado.

    Nota: La función IFNULL se utiliza para manejar los casos en los que una orden puede no tener ningún artículo. En tales casos, la cantidad de artículos de la orden será nula, por lo que utilizamos la función IFNULL para reemplazarla por 0.

Comments are closed.