How you can generate blog archive links in laravel

Saif Shakil Laravel 27 December, 2017 : 10:26 AM

How you can generate blog archive links in laravel

In this tutorial, i will try to illustrate how you can create blog archive sorted by month, year with the count of total article published on that month. So lets get started. 


First lets think what we want to get as output right? So if we try to build query with raw SQL.

select
year(created_at) as year,
month(created_at)as month,
MONTHNAME(created_at) month_name,
count(*) total_published
From articles
where status=1
GROUP BY year, MONTH(created_at), MONTHNAME(created_at)
ORDER BY year DESC, month DESC;

After Executing this query we will get output like this -

YearMonthMonth NameTotal Published
20179September2
201710October2
201711November2
201712December2

So how we can write this in laravel query builder format? lets have a look here - 

    $archives = DB::table('articles')
->selectRaw('year(created_at) as year,month(created_at) as month, monthname(created_at) as monthname, COUNT(*) post_count')
->groupBy('year')
->groupBy('monthname')
->groupBy('month')
->orderBy('year', 'desc')
->orderBy('month', 'desc')
->where('status',1)
->get();

You can check the what you have got in $archives variable by dd($archive). Then for generating links for each month,year we have to run foreach loop here. Something like this - 

<ul class="link_nav">
<li>
@foreach($archives as $archive)
<a href="{{ route('archives', ['month' => $archive->monthname, 'year'=>$archive->year]) }}">
{{ $archive->monthname }}, {{ $archive->year }} <b class="badge">{{ $archive->post_count }}b>
a>
@endforeach
li>
ul>

Here in route we are passing month and year to controller so that we can filter out the result. Closely look at month, here we are passing month name as string for SEO purpose. We will deal with it in our controller. So first declare the route like this - 

Route::get('/archives', ['as' => 'archives', 'uses' => 'Blogs@archives']);

We will get url like this - https://www.sshakil.com/archives?month=December&year=2017

Now Lets look at our controller - 

public function archives(Request $request)
{
$month = $request->month;
$year = $request->year;

$data['archive_name'] = 'Archives from '.$month.', '.$year;

$articles = Article::latest();
$articles->whereMonth('created_at', Carbon::parse($month)->month);
$articles->whereYear('created_at', $year);

$data['articles'] = $articles->paginate(10);

return view('frontEnd.blogs.archives', $data);

}


Here we are using whereMonth(), whereYear() method for filtering the result by month, year. Remember we got our month name as string e.g. December/January. But Here we need the month value as integer. Here comes Carbon::parse. Very useful at this point. Carbon::parse($month)->month by doing this we are converting december to 12. 

So this is how we get articles after filtering. passing $articles to blade view and We need to run foreach loop to get all the articles from the specific month,year.


TAGS: SQL Query Laravel 5.5 PHP MySql
Views: 851

Related Posts you may like