For my site admin panel, i need to show statistics for payments between two date
My payments table fields:
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| price | int(11) | NO | | 0 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
I need to get the payments data between tow days, day by day by php laravel to this structure
// 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
],
];
You should know maybe a day i haven't any payment and in database this day hasen't any records. but i need to show this day with count of 0
I don't know how do this
Using a recursive common table expression (in mysql 8 or mariadb 10.2+) to create your table of dates (here, reporting dates from 2021-01-01 to 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;
You can do that using this query:
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;
if you want laravel query builder version:
$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();
ps: the date range subquery I taken from here
You can use DB::raw():
$data = DB::raw('select left(created_at, 10) as date, count(1) as count from payments group by date;');