php - 在 Laravel 中为博客生成存档列表

Question

我正在尝试为博客文章生成存档列表。存档列表应按时间倒序显示年份和日期，如下所示:

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

() 中的数字是该时间段内的帖子数。选择年或月时，应仅显示所选时间段内的博文。

到目前为止，我只能通过以下方式找出每篇博文的年份和月份:

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

我如何着手实现上述目标？

Answer 1

为了在某种导航面板中生成链接，您可以在数据库端完成大部分处理，而不是使用这样的查询获取所有博客文章记录

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 的专家，但应该可以用类似的东西来实现

$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();

如果你愿意，你可以像这样将小计添加到年份行

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