Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generating archive list for a blog in Laravel

Tags:

sql

php

laravel

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?

like image 841
SUB0DH Avatar asked May 11 '13 07:05

SUB0DH


3 Answers

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

like image 98
peterm Avatar answered Sep 21 '22 02:09

peterm


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
like image 22
Jack McDade Avatar answered Sep 21 '22 02:09

Jack McDade


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

like image 29
Raymond Wensveen Avatar answered Sep 20 '22 02:09

Raymond Wensveen