问题描述
我有一个雄辩的查询,可以获取过去 7 天的总计数记录 (created_at).但问题是如果其中一天有 0 条记录,这不会出现在最终数据中.
我的查询:
$data = Data::whereBetween('created_at', [Carbon::now()->subDays(6)->format('Ym-d')." 00:00:00", Carbon::now()->format('Ym-d')." 23:59:59"])-> groupBy('日期')-> orderBy('日期')->get([DB::raw('DATE(created_at) 作为日期'),DB::raw('count(*) as total')])-> pluck('total', 'date')->toArray();
我得到了什么:
[2020-04-14" =>12020-04-16" =>12020-04-18" =>12020-04-19" =>1]我的预期:
[2020-04-14" =>12020-04-15" =>02020-04-16" =>12020-04-17" =>02020-04-18" =>12020-04-19" =>12020-04-20" =>0]有什么建议吗?
解决方案:
-基于 Gary Houbre 的提议:
$results = Data::whereBetween('created_at', [Carbon::now()->subDays(6)->format('Ym-d')." 00:00:00", Carbon::now()->format('Ym-d')." 23:59:59"])-> groupBy('日期')-> orderBy('日期')->get([DB::raw('DATE_FORMAT(created_at, "%Y-%m-%d") 作为日期'),DB::raw('count(*) as total')])->keyBy('日期')-> 地图(功能($item){$item->date = Carbon::parse($item->date);返回 $item;});$period = new DatePeriod(Carbon::now()->subDays(6), CarbonInterval::day(), Carbon::now()->addDay());$graph = array_map(函数($datePeriod)使用($results){$date = $datePeriod->format('Y-m-d');返回 $results->has($date) ?$results->get($date)->total : 0;}, iterator_to_array($period));
直接看 Sql : 如何包含零";/0"结果是 COUNT 个聚合?
进入同一张表:如何如果 Laravel 中 Count 为零,则获取记录
您需要使用 Eloquent 在您的请求中添加外部联接.
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.
My query:
$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();
What I get:
[
"2020-04-14" => 1
"2020-04-16" => 1
"2020-04-18" => 1
"2020-04-19" => 1
]
What I expected:
[
"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
]
Any suggestions?
SOLUTION:
-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));
Looking directly Sql : How to include "zero" / "0" results in COUNT aggregate?
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.
这篇关于如何获取最近 7 天的记录为 0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!