如何在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()我希望这对你有用...