本文介绍了如何获取0计数的最近7天记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有个雄辩的查询,它获取最近7天的总计数记录(created_at)。但是问题是,如果这些日子之一中有0条记录,那么它不会出现在最终数据中。
I have an eloquent query that gets the total count records (created_at) of the last 7 days. But the problem is if one of these days have 0 records, this doesn't appear in the final data.
我的查询:
$data = Data::whereBetween('created_at', [Carbon::now()->subDays(6)->format('Y-m-d')." 00:00:00", Carbon::now()->format('Y-m-d')." 23:59:59"])
->groupBy('date')
->orderBy('date')
->get([
DB::raw('DATE(created_at) as date'),
DB::raw('count(*) as total')
])
->pluck('total', 'date')->toArray();
我得到的东西:
[
"2020-04-14" => 1
"2020-04-16" => 1
"2020-04-18" => 1
"2020-04-19" => 1
]
我的期望:
[
"2020-04-14" => 1
"2020-04-15" => 0
"2020-04-16" => 1
"2020-04-17" => 0
"2020-04-18" => 1
"2020-04-19" => 1
"2020-04-20" => 0
]
有任何建议吗?
解决方案:
-基于Gary Houbre的建议:
-Based on Gary Houbre's proposal:
$results = Data::whereBetween('created_at', [Carbon::now()->subDays(6)->format('Y-m-d')." 00:00:00", Carbon::now()->format('Y-m-d')." 23:59:59"])
->groupBy('date')
->orderBy('date')
->get([
DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d") as date'),
DB::raw('count(*) as total')
])
->keyBy('date')
->map(function ($item) {
$item->date = Carbon::parse($item->date);
return $item;
});
$period = new DatePeriod(Carbon::now()->subDays(6), CarbonInterval::day(), Carbon::now()->addDay());
$graph = array_map(function ($datePeriod) use ($results) {
$date = $datePeriod->format('Y-m-d');
return $results->has($date) ? $results->get($date)->total : 0;
}, iterator_to_array($period));
推荐答案
直接查看Sql:
Into a same table : How to get the record if Count is zero in Laravel
You need to add an outer join into your request with Eloquent.
这篇关于如何获取0计数的最近7天记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!