如何在laravel中获取每个月的帖子计数

Question

我想在我的laravel应用程序中制作图表，我想显示每个月创建了多少帖子，所以我需要一种如下的API：{count:22,month:1-1-2020},{count:18,month:1-2-2020}这是我试图做的：       return Post::all()->groupBy(function($post) { // Get all posts as collection and apply groupBy method            $post->created_at->format('F'); // ex: September        });但是按月份对计数进行分组不起作用，所以有没有办法实现这一点？

Answer 1

您可以尝试按日期汇总，使用面具 ：DATE_FORMAT%Y-%m

$results = Post::select(\DB::raw("DATE_FORMAT(created_at, '%Y-%m') AS ym, COUNT(*) AS cnt"))

->groupBy(\DB::raw("DATE_FORMAT(created_at, '%Y-%m')"))

->get();

如果您的实际数据库是Postgres，那么使用类似的方法应该有效：TO_CHAR

$results = Post::select(\DB::raw("TO_CHAR(created_at, 'YYYY-MM') AS ym, COUNT(*) AS cnt"))

->groupBy(\DB::raw("TO_CHAR(created_at, 'YYYY-MM')"))

->get();

Answer 2

给你

return Post::select(DB::raw('count(1) AS count'), DB::raw('DATE_FORMAT(created_at, "01-%m-%Y") AS month'))->groupBy(function($post) { // Get all posts as collection and apply groupBy method

     $post->created_at->format('01-m-Y'); // ex: 01-02-2019

})->get()

我希望这对你有用...