How to get this query using the Laravel query builder?

Hi everyone,

I'm new to joins and I created this query:

select 
    threads.*, count(replies.id) replies_count
from threads
where replies_count = 0
left join replies
on replies.thread_id = threads.id
group by threads.id

How do I get this query into a Laravel Query Builder object?

This is what I was trying (but I got stuck):

$builder
    ->selectRaw('threads.*, count(replies.id) replies_count')
    ->leftJoin('replies', 'replies.thread_id', '=', 'threads.id')
    ->groupBy('threads.id')
    ->where('replies_count', '=', 0);

Thank you! Jeroen

tykus

5 years ago

Best Answer

Level 104

If you choose to use Eloquent Models, then you can count the replies relationship where the Thread has replies:

Thread::doesntHave('replies')->withCount('replies')->get();

EDIT you wanted thread where there are no replies, so use doesntHave - you could exclude the withCount in that case

jeroenvanrensen

5 years ago

Level 15

@tykus Thank you!

Please or to participate in this conversation.