Count from each distinct date, fill in missing dat

Question

I'm trying to create an Eloquent query that gets the total number of posts made each distinct day, and if the date is missing, fill it in with a value of zero.

For example, if my table looks like this:

+----+---------------------+
| id |        date         |
+----+---------------------+
|  1 | 2015-01-01 00:00:00 |
|  2 | 2015-01-01 01:53:18 |
|  3 | 2015-01-01 02:41:26 |
|  4 | 2015-01-02 12:51:01 |
|  5 | 2015-01-05 08:24:12 |
+----+---------------------+

This would output:

2015-01-01 : 3
2015-01-02 : 1
2015-01-05 : 1

Notice, however, that the days 03-04 are missing. How can I include these dates, but give them the value 0 such that I end up with an output like:

2015-01-01 : 3
2015-01-02 : 1
2015-01-03 : 0
2015-01-04 : 0
2015-01-05 : 1

Here is my current query:

$posts = Post::select(array(
        DB::raw('DATE(`created_at`) as `date`'),
        DB::raw('COUNT(*)as `count`')
    ))
    ->where('created_at', '>', Carbon::today()->subWeek())
    ->groupBy('date')
    ->orderBy('date', 'DESC')
    ->lists('count', 'date');

Thanks!

Answer 1

In your SQL results you can generate some "fake-data" in your rows, but u can not generate "fake-rows", exept joining to some "fake(temporary)-table". In your case ll be much easier to apply some logic around sql-result.

Replace your code with this:

$order              = 'DESC';
$endDate            = Carbon::today();
$startDate          = Carbon::today()->subWeek();

$dateInc            = ($order == 'DESC') ? -1 : 1;
$dateCycleHolder    = clone ($dateInc > 0 ? $startDate : $endDate);
$dateCycleEnd       = clone ($dateInc > 0 ? $endDate : $startDate);


$posts = Post::select(array(
    DB::raw('DATE(`created_at`) as `date`'),
    DB::raw('COUNT(*)as `count`')
))
    ->where('created_at', '>', $startDate)
    ->groupBy('date')
    ->orderBy('date', $order)
    ->lists('count', 'date');

$postsFinal = new \Illuminate\Database\Eloquent\Collection();

while ($dateCycleHolder->ne($dateCycleEnd)) {
    $dateCurr = $dateCycleHolder->format('Y-m-d');
    $postsFinal->put($dateCurr, $posts->get($dateCurr, 0));
    $dateCycleHolder->addDay($dateInc);
}
$dateCurr = $dateCycleHolder->format('Y-m-d');    
$postsFinal->put($dateCurr, $posts->get($dateCurr, 0));

$posts = $postsFinal;

its alittle bit flexible, you can change values of this things:

$order              = 'DESC';
$endDate            = Carbon::today();
$startDate          = Carbon::today()->subWeek();