How to get last 7 days records with 0 counts [duplicate]

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?

Answer 1

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.

Answer 2

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.