如何获取0计数的最近7天记录 [英] How to get last 7 days records with 0 counts

问题描述

我有个雄辩的查询，它获取最近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：如何包含零 / 0

进入同一表格：

Into a same table : How to get the record if Count is zero in Laravel

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

You need to add an outer join into your request with Eloquent.

这篇关于如何获取0计数的最近7天记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！