Support the ongoing development of Laravel.io →
posted 4 years ago
Last updated 2 years ago.
0

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.

Last updated 4 years ago.
0

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-related-models

0

Error Code: 1242. Subquery returns more than 1 row

0

Can you log and post the resulting query here?

0

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

Last updated 4 years ago.
0

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.

0

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`
Last updated 4 years ago.
0

I used fromSub method, I think it's the best way

uteqnl liked this reply

1

Looks like a solid solution. Thanks for sharing this.

mohammadagbareya liked this reply

1

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

Last updated 4 years ago.

mohammadagbareya liked this reply

1

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.