Para el panel de administración de mi sitio, necesito mostrar estadísticas de pagos entre dos fechas
Los campos de mi tabla de pagos:
+--------------------+--------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | price | int(11) | NO | | 0 | | | created | timestamp | NO | | CURRENT_TIMESTAMP | | +--------------------+--------------+------+-----+-------------------+-----------------------------+Necesito obtener los datos de pagos entre dos días, día a día por php laravel a esta estructura
// date between 2021-12-01 and 2021-12-03 $data = [ [ 'date' => '2021-12-01', 'count' => 5 // number of payments records in 2021-12-01 in payments table ], [ 'date' => '2021-12-02', 'count' => 0 // number of payments records in 2021-12-01 in payments table ], [ 'date' => '2021-12-03', 'count' => 15 // number of payments records in 2021-12-01 in payments table ], ];Debería saber que tal vez un día no tengo ningún pago y en la base de datos ese día no hay ningún registro. pero necesito mostrar este día con cuenta de 0
no se como hacer esto
Usando una expresión de tabla común recursiva (en mysql 8 o mariadb 10.2+) para crear su tabla de fechas (aquí, informando fechas desde 2021-01-01 hasta 2021-01-31):
with recursive dates as ( select date('2021-01-01') date union all select dates.date + interval 1 day from dates where dates.date < '2021-01-31' ) select dates.date, count(payments.id) from dates left join payments on date(payments.created)=dates.date group by 1;Puedes hacerlo usando esta consulta:
select gen_date, count(transactions.id) from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v left JOIN transactions on date(transactions.created) = date(gen_date) where gen_date between '2021-12-01' and '2021-12-20' group by gen_date;si desea la versión del generador de consultas de laravel:
$select = DB::query()->selectRaw("gen_date, count(transactions.id)")->fromSub(function ($query) { $query->select("(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4)"); }, "v") ->leftJoin('transactions', function ($join) { $join->on(DB::raw("date(transactions.created)"), "=", DB::raw("date(v.gen_date)")); }) ->whereRaw("gen_date between '2021-12-01' and '2021-12-20'") ->groupBy('gen_date') ->get();pd: la subconsulta de rango de fechas que tomé de aquí
Puedes usar DB::raw() :
$data = DB::raw('select left(created_at, 10) as date, count(1) as count from payments group by date;');