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

问题描述

我有一个雄辩的查询，可以获取过去 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！