How to use withCount and groupBy month in Laravel Eloquent?-CodePudding

I am trying to count the number of blogs per month and the number of posts per blog in one query:

Blog::withCount(['posts'])
    ->whereBetween('created_at', $this->currentRange())
    ->select(DB::raw("DATE_FORMAT(created_at, '%b') month"), DB::raw('COUNT(*) num'), DB::raw('SUM(posts_count) sum'))
    ->groupBy('month')
    ->get();

To get the following result:

 --------- ------- -------- 
|  month  |  num  | posts  |
 --------- ------- -------- 
| Jan     |  10   | 5      |
| Feb     |  20   | 6      |
| Mar     |  30   | 7      |
 --------- ------- --------

The following SQL is generated by eloquent:

SELECT
  DATE_FORMAT(created_at, '%b') MONTH,
  COUNT(*) num,
  SUM(posts_count) sum
FROM
  `blogs`
WHERE
  `created_at` BETWEEN 0001 -01 -01 00: 00: 00
  AND 2023 -01 -05 21: 10: 59
  AND `blogs`.`deleted_at` IS NULL
GROUP BY
  `MONTH`

Which is missing the posts_count completely. I guess I expected some magic from eloquent. Is there an eloquent way of doing that? Or is a raw query the way to go here?

CodePudding user response：

You can use a subquery.

Blog::withCount(['posts'])
    ->select(DB::raw("DATE_FORMAT(blogs.created_at, '%b') month"), DB::raw('COUNT(*) num'), DB::raw("SUM(posts_count) sum"))
    ->join(DB::raw("(SELECT blog_id, SUM(posts.id) sum_posts FROM posts WHERE posts.created_at BETWEEN blogs.created_at AND blogs.created_at   INTERVAL 1 MONTH GROUP BY blog_id) as posts_per_blog"), function($join) {
        $join->on('blogs.id', '=', 'posts_per_blog.blog_id');
    })
    ->whereBetween('blogs.created_at', $this->currentRange())
    ->groupBy('month')
    ->get();