How to get last 7 days records with 0 counts

Question

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));

Gary Houbre · Accepted Answer · 2020-04-20T12:52:32.613

2

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.

edited Apr 20 '20 at 12:52

answered Apr 20 '20 at 12:25

Gary Houbre

857
8
24

I am not joining two tables, I am working on the same table. I'm not sure if this would work. Thanks anyway I will try it! – Alvaro_SE Apr 20 '20 at 12:41

score 2 · Answer 2 · answered Apr 20 '20 at 12:33

2

My idea is to create a for loop to check the days.

If there is no record on a date then print 0

Loop Iteration:

Catch the first Day (Suppose 14)
Catch the last Day
Then check in every iteration it is greater than one or many

Thus, I hope you will get normally.

answered Apr 20 '20 at 12:33

Mr. Perfectionist

2,605
2
24
35

Thanks! But this is my last option, I'm trying to find a automatic option. But thanks for your help! – Alvaro_SE Apr 20 '20 at 12:40

score 1 · Answer 3 · answered Apr 20 '20 at 13:33

We had a similar problem while trying to put back-end data into the chart. Since some of the days were missing it didn't look well. Our solution was;

Create a function like this;

public function generateDates(Date $startDate, Date $endDate, $format = 'Y/m/d'): Collection
{
    $dates = collect();
    $startDate = $startDate->copy();

    for ($date = $startDate; $date->lte($endDate); $date->addDay()) {
        $dates->put($date->format($format), 0);
    }

    return $dates;
}

In your case it's going to be (today and today - six days) and you will union returning collection with your query collection. What it does is; it create a date range from the keys and fill them with zero. When your query collection has some value other than zero - it is going to overwrite it.

Yes, I have something similar to collect de dates of the previous 6 days from today. Later, for use in the solution propossed by @Mr. Perfectionist. I've added the solution int the post, if it helps you. — Alvaro_SE, Apr 20 '20 at 13:39

How to get last 7 days records with 0 counts

3 Answers3

Linked