Eloquent get by related table count

I created messenger for laravel. Now I wanna list all threads in which user is participating with a count of messages in each thread. I need the count to where clause because I want to show only these threads, in which are messages.

where

My current query:

$threads = Participant::with('thread.messages') -> where('user_id', Auth::user() -> id) -> get();

$threads = Participant::with('thread.messages') -> where('user_id', Auth::user() -> id) -> get();

Participant:

public function user()
{
return $this -> hasOne(User::class, 'id', 'user_id');
}
public function thread()
{
return $this -> hasOne(Thread::class, 'id', 'thread_id');
}


Thread:

public function participants()
{
return $this -> hasMany(Participant::class, 'thread_id', 'id');
}
function messages()
{
return $this -> hasMany(Message::class, 'thread_id', 'id');
}


Message:

function user()
{
return $this -> belongsTo(User::class, 'user_id', 'id');
}


Thanks!

2 Answers
2

You can use has for messages relation to check thread has message. After that you need to filter the participants relationship using
whereHas And at last use withCountfor counting message in thread.

has

messages

whereHas

withCount

$threads = Thread::has('messages')
->whereHas('participants', function($query){
return $query->where('user_id', Auth::user()->id);
})->withCount('messages')->get();


Print data

foreach($threads as $thread){
dd($thread->messages_count);
}


Note: Your relation name thread in Participant model should be belongsTo instead hasOne.

thread

Participant

belongsTo

hasOne

Try using withCount for the thread messages. :

withCount

$threads = Participant::withCount(['thread.messages as message_count'])
->where('user_id', auth()->id())
->having('message_count', '>', 0)
->get();


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.