Laravel generate archive post by year and month

Question

I would now recover my articles with an archive system ( list ) year and month with Laravel 4.2 .

I manage to retrieve my items per year and per month with no problem (see below)

Here is my controller using the Query builder Laravel

public function index(){
 $post_links = DB::table('posts')
          ->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();

          return View::make('home.index')->with(array(
          'post_links'=>$post_links,

      ));
}

And my view

 <ul id="show-year">
                @foreach($post_links as $link)
                {{--{{dd($link)}}--}}
                 {{--Show year--}}
                     <li>
                          <a title="" href="#">
                              <span>
                                <strong>{{$link->year}}</strong></a>&nbsp;<span class="post-count">{{$link->post_count}}</span><i class="fa fa-arrow-right"></i>
                              </span>
                              </li>
                          </a>
                          {{--show month--}}
                          <ul id="show-month">
                            <li class="month-content">
                               <a href="">
                                  <span>
                                    <strong>{{$link->month_name}}</strong>
                                  </span>
                            </a>
                            </li>
                          </ul>
                       </li>
                @endforeach
        </ul>

I recovered my data years and months but it adds a year each month to her together in this way

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

I don't understand why it does not group me in the same year and generates me every time a new entry of year for each month.

Thanks for your help.

when i debug $posts_links i have aan array of object like that array (size=4) 0 => object(stdClass)[362] public 'year' => string '2016' (length=4) public 'month' => string '1' (length=1) public 'month_name' => string 'January' (length=7) public 'post_count' => string '1' (length=1) My year i not group — nicolassiuol

Muhamad Zolfaghari Muhamad Zolfaghari · Accepted Answer · 2016-07-28T17:10:53

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 |

something like this code

  $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('yea`enter code here`r', 'desc')
        ->orderBy('month', 'desc')
        ->get();

original solution is here

Laravel generate archive post by year and month

1 Answers