在Laravel中为博客生成存档列表 [英] Generating archive list for a blog in 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));
}

我如何实现上述目标?

推荐答案

要在某种导航面板中生成链接，您可以在数据库端进行大部分处理，而不必通过这样的查询获取所有博客文章记录

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;

输出:

| 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 |

我不是laravel的专家，但是应该使用类似的方法实现

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;

输出:

| 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

SQLFiddle

这篇关于在Laravel中为博客生成存档列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！