在laravel中显示每周的每一天的帖子计数

Question

I have a function in my controller with this:

$one_week_ago = Carbon::now()->subDays(6)->format('Y-m-d');
$dates = Post::where('created_at', '>=', $one_week_ago)
        ->groupBy('date')
        ->orderBy('date', 'ASC')
        ->get(array(
             DB::raw('Date(created_at) as date'),
             DB::raw('COUNT(*) as "count"')
          ));

return view ('analytics', array('dates' => $dates));

And my view is:

@foreach ($dates as $date => $count) 
      <p> {{ $date . '->' . $count }}</p>
@endforeach

I am getting the output as:

0 -> {"date":"2017-04-07","count":7}

1 -> {"date":"2017-04-08","count":2}

2 -> {"date":"2017-04-12","count":4}

3 -> {"date":"2017-04-13","count":1}

Problem: The dates in which has no posts (i.e postscount = 0) those are not shown. Example: 2017-04-09, 2017-04-10 and 2017-04-11 are not shown.

But I want the output for those dates as count:0

Answer 1

One approach to tackle this is to build a collection containing the default values first (keys are the dates you're looking at, and the values are 0). This gives you an array that looks like this:

[
    '2017-04-07' => 0,
    '2017-04-08' => 0,
    '2017-04-09' => 0,
    '2017-04-10' => 0,
    '2017-04-11' => 0,
    '2017-04-12' => 0,
    '2017-04-13' => 0,
]

Then we take the posts we found and call pluck - this lets us specify the value we want (count) and the key we'd like to use (date). This won't necessarily have all of the days we want in it, and will look something like this:

[
    '2017-04-07' => 12,
    '2017-04-10' => 3,
    '2017-04-11' => 7,
]

Finally, we merge the two based on the keys, using the first collection as the base. This means we get to keep all the default values we set up (and more importantly, all 7 dates we set) and it just overwrites individual day values with the results from the database.

---

Here's the final code:

// Build an array of the dates we want to show, oldest first
$dates = collect();
foreach( range( -6, 0 ) AS $i ) {
    $date = Carbon::now()->addDays( $i )->format( 'Y-m-d' );
    $dates->put( $date, 0);
}

// Get the post counts
$posts = Post::where( 'created_at', '>=', $dates->keys()->first() )
             ->groupBy( 'date' )
             ->orderBy( 'date' )
             ->get( [
                 DB::raw( 'DATE( created_at ) as date' ),
                 DB::raw( 'COUNT( * ) as "count"' )
             ] )
             ->pluck( 'count', 'date' );

// Merge the two collections; any results in `$posts` will overwrite the zero-value in `$dates`
$dates = $dates->merge( $posts );

return view( 'analytics', compact( 'dates' ) );

Your view then should simply be

@foreach ( $dates as $date => $count ) 
    <p> {{ $date }} = {{ $count }}</p>
@endforeach