Generating archive list for a blog in Laravel

Question

I am trying to generate an archive list for blog articles. The archive list should display year and date in reverse chronological order as follows:

2013 (21)
    - May (2)
    - April (3)
    - March (5)
    - February (1)
    - January (10)
2012 (10)
    - December (6)
    - November (4)

The number inside () are the number of posts in that time period. When the year or month of the year has been selected, only the blog posts from that selected time period should be displayed.

So far I've only been able to find out the year and month of each blog post by doing:

$posts = Post::all();
$archive = array();
foreach ($posts as $post) {
    $year = date('Y', strtotime($post->created_at));
    $month = date('m', strtotime($post->created_at));
}

How do I go about achieving the above objectives?

Answer 1

For generating a links in some sort of navigation panel you can do most of the processing on DB side and not fetching all the blog posts records with a query like this

SELECT YEAR(created_at) year,
       MONTH(created_at) month,
       MONTHNAME(created_at) month_name,
       COUNT(*) post_count
  FROM post
 GROUP BY year, MONTH(created_at)
 ORDER BY year DESC, month DESC;

Output:

| YEAR | MONTH | MONTH_NAME | POST_COUNT |
------------------------------------------
| 2013 |     5 |        May |          5 |
| 2013 |     4 |      April |          3 |
| 2013 |     3 |      March |          4 |
| 2013 |     2 |   February |          3 |
| 2013 |     1 |    January |          2 |
| 2012 |    12 |   December |          2 |
| 2012 |    11 |   November |          3 |

I'm not an expert in laravel, but it should be achieved with something similar to this

$links = DB::table('post')
    ->select(DB::raw('YEAR(created_at) year, MONTH(created_at) month, MONTHNAME(created_at) month_name, COUNT(*) post_count'))
    ->groupBy('year')
    ->groupBy('month')
    ->orderBy('year', 'desc')
    ->orderBy('month', 'desc')
    ->get();

If you want you can add subtotals to year rows like this

SELECT YEAR(created_at) year,
       MONTH(created_at) month,
       MONTHNAME(created_at) month_name,
       COUNT(*) post_count
  FROM post
 GROUP BY year, MONTH(created_at)
UNION ALL
SELECT YEAR(created_at) year,
       13 month,
       NULL month_name,
       COUNT(*) post_count
  FROM post
 GROUP BY year
 ORDER BY year DESC, month DESC;

Output:

| YEAR | MONTH | MONTH_NAME | POST_COUNT |
------------------------------------------
| 2013 |    13 |     (null) |         17 |
| 2013 |     5 |        May |          5 |
| 2013 |     4 |      April |          3 |
| 2013 |     3 |      March |          4 |
| 2013 |     2 |   February |          3 |
| 2013 |     1 |    January |          2 |
| 2012 |    13 |     (null) |          5 |
| 2012 |    12 |   December |          2 |
| 2012 |    11 |   November |          3 |

SQLFiddle

Answer 2

This is the most elegant way to do this would be to pass a closure into the groupBy() collection method.

$posts_by_date = Post::all()->groupBy(function($date) {
    return Carbon::parse($date->created_at)->format('Y-m');
});

Then you can loop through it in your blade template similar to this:

@foreach ($posts_by_date as $date => $posts)
    <h2>{{ $date }}</h2>
    @foreach ($posts as $post)
        <h3>{{ $post->title }}</h3>
        {{ $post->content }}
    @endforeach
@endforeach

Answer 3

I was stuck on this for a while as well. By using some more laravel functionality I came to the following solution:

To create the archive:

$archive = Post::orderBy('created_at', 'desc')
        ->whereNotNull('created_at')
        ->get()
        ->groupBy(function(Post $post) {
            return $post->created_at->format('Y');
        })
        ->map(function ($item) {
            return $item
                ->sortByDesc('created_at')
                ->groupBy( function ( $item ) {
                    return $item->created_at->format('F');
                });
        });

Then to Display (including bootstrap 4 classes):

<div class="archive mt-5">
<h4>Archive</h4>

@foreach($archive as $year => $months)
    <div>
        <div id="heading_{{ $loop->index }}">
            <h6 class="mb-0">
                <button class="btn btn-link py-0 my-0" data-toggle="collapse"
                        data-target="#collapse_{{ $loop->index }}"
                        aria-expanded="true"
                        aria-controls="collapse_{{ $loop->index }}">
                    >
                </button>
                {{ $year }}
            </h6>
        </div>

        <div id="collapse_{{ $loop->index }}" class="collapse" aria-labelledby="heading_{{ $loop->index }}"
             data-parent="#accordion">
            <div>
                <ul style="list-style-type: none;">
                    @foreach($months as $month => $posts)
                        <li class="">
                                {{ $month }} ( {{ count($posts) }} )
                        </li>

                    @endforeach
                </ul>
            </div>
        </div>
    </div>
@endforeach