gpt4 book ai didi

laravel - 如何获取计数为 0 的最近 7 天的记录

转载 作者:行者123 更新时间:2023-12-05 02:07:59 25 4
gpt4 key购买 nike

我有一个 Eloquent 查询,它获取过去 7 天的总计数记录 (created_at)。但问题是如果其中一天有 0 条记录,这不会出现在最终数据中。

我的查询:

$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 的提议:

$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:How to include "zero" / "0" results in COUNT aggregate?

进入同一张表:How to get the record if Count is zero in Laravel

您需要使用 Eloquent 将外部连接添加到您的请求中。

关于laravel - 如何获取计数为 0 的最近 7 天的记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61322417/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com