Laravel get count of users they commented on a post-CodePudding

I have some post records and each one has many comments that recorded in another table called comments and each comment belongs to a user

How can I get a list of posts with number of users they commented on each post?

-- A post can have multiple comments via one user, so the number of comments is not right, I'm looking for number of users.

I tried:

$posts = Post::query()
               ->where('category',$request->input('category'))
               // other conditions
               ->get();

$numberOfUsers=[];
forech($posts as $post){
    $numberOfUsers[$post->id] = $post->comments()->groupBy('user')->count();
}

But I don't know if it's the best way or not.

I have used hasMany and hasManyThrough with groupBy, but still getting number of comments instead of number of users.

CodePudding user response：

Create userComments relation on Post model:

return $this->hasMany(Comment::class)->group_by('comments.user_id');

then try this query:

Post::withCount('userComments')->where('category',$request->input('category'))->get();

Not sure this is the best way, maybe some people with more experience will comment below

CodePudding user response：

wise man once asked,

cant you solve this using withCount()?

The reason being withCount() can be used to count the numbers of related entries. Its really depends on how you make the relation between each models.

You can for one define a relationship between Post and User as a many-to-many relationship using Comment as pivot and hence:

class Post extends Model
{
    ... a bunch of unrelated stuff

    public function comment_users()
    {
      return $this->belongsToMany(User::class, 'comments', 'post_id', 'user_id');
    }
}

And then run an Eloquent query this way:

App\Models\Post::withCount('comment_users')->get()

Which generates the following query:

select `posts`.*,
       (
        select count(*)
        from `users`
        inner join `comments`
        on `users`.`id` = `comments`.`user_id`
        where `posts`.`id` = `comments`.`post_id`
       ) as `comment_users_count`
from `posts`

In which you can see the count result in property called comment_users_count in the retrieved Post's model.

One can always tarnish themselves with DB::raw() as a barbaric means to defile the Eloquent ways.

Though, to be fair, some might complain the resulting query is suboptimal and they can write better query - but its a power we left for enormous convenience called ORM.