for the date filtering you can make a scope. For example scopeInMonth($year, $month).

I didn't try the query, but it should be along these lines

User::withCount('comments', function ($commentQuery){return $commentQuery->inMonth(2019,12)->groupBy('commentable_id', 'commentable_type', 'user_id');})->get();

Also be aware that grouping by commentable_id isn't enough. Anything could be commentable and the IDs can overlap. Like a commentable Post and commentable Comment can have the same ID, in which case they get counted together.

Be aware that this will execute two queries, one to fetch the users and the the other to fetch the comment count. You will probably add more wheres to the main query. More details at https://laravel.com/docs/7.x/eloquent-relationships#counting-r...

Error Code: 1242. Subquery returns more than 1 row

Can you log and post the resulting query here?

SELECT id, name, (SELECT COUNT(*) FROM commentsWHEREusers.id=comments.user_idANDcreated_at>= '2019-12-01' ANDcreated_at<= '2019-12-31' GROUP BYcommentable_id,commentable_type,user_id) AS comments_countFROMusersWHEREusers.deleted_at IS NULL

Instead of two nested subqueries you should go for 'count (distinct commentable_id, commentable_type)'. That should be faster. Drop the group by. Not sure how to acheieve it in Eloquent. Try to replace groupBy with distinct.

Here's my two cents (not looking at performance). By the way this is wat Laravel docs says about it:

$users = User::query()->withCount(['comments' => function(Builder $query) {
    $query->whereMonth('created_at', '6');
}])->get();

echo $users[0]->comments_count;

This will build this query (i simplified it):

select 
  `users`.*, 
  (select count(*) from `comments` where `users`.`id` = `comments`.`user_id` and month(`created_at`) = 6) as `contracts_count` 
from `employees`

Looks like a solid solution. Thanks for sharing this.

This very great information for me thanks for sharing ....i helped me a lot FE