Using withCount() on a relationship that has distinct()

Question

I have the following models:

Question: [title, user_id]

Reply: [body, question_id, user_id]

User: [name]

As you can see, a question has many replies, and a reply belongs to a user.

I've added a contributors relationship to the Question model that retrieves all the users who've added a reply (using the replies as the join table):

public function contributors() 
{
    return $this->belongsToMany(User::class, 'replies')->distinct('user_id');
}

I had to use distinct() to remove duplicates because a user might post many replies on a single question and this works fine.

Now the problem happens when I do:

Question::withCount('contributors')->get()

It ignores the call to distinct() and gives me the total number of users who've added a reply (including duplicates).

Any idea how I can fix this?

Answer 1

Remove distinct() from the relationship and use withCount() with a raw expression:

public function contributors() 
{
    return $this->belongsToMany(User::class, 'replies');
}

Question::withCount(['contributors' => function ($query) {
    $query->select(DB::raw('count(distinct(user_id))'));
}])->get();