Get Result By Parent Tables Status

I have three tables all of them has Model extends from Model

Users => it has active(bool) column and it is parent of posts

Posts => it has active(bool) column and it is parent of comments

Comments => it has active(bool) column

So i want to get all comments that is active true also parent table must be true also parent of posts (users) column active must be true. I want to able to do it using elequent belongsTo/HasMany doesnt matter actually.

I made pure sql query for all you to understan what i want to do;

select `comments` .* from `comments` 
inner join `posts` on `posts`.`id` = `comments`.`post_id` and `posts`.`active` = 1
inner join `users` on `users`.`id` = `posts`.`user_id` and `users`.`active` = 1
where `comments`.`active` = 1;

This is my first time here hello tto all laracasts members.

tykus

2 years ago

Best Answer

Level 104

If you want to produce exactly the SQL above using Eloquent:

Comment::query()
    ->where('active', true)
    ->join('posts', fn($join) => $join->on('posts.id', '=', 'comments.post_id')->where('posts.active', true))
    ->join('users', fn($join) => $join->on('users.id', '=', 'posts.user_id')->where('users.active', true))
    ->get();

Eloquent has a whereHas (or more recent whereRelation) methods to constraint a query across relations. However, the queries produced are not going to be JOINs, and will likely be less performant than the example above; e.g.

Comment::query()
    ->whereRelation('post', 'active', true)
    ->whereRelation('post.user', 'active', true)
    ->get();

1 like

cemilakan

2 years ago

Level 1

@tykus Second one use a lot where exists :( in the end i will have to use old style join method i think :) thanks for answer

tykus

2 years ago

Level 104

@cemilakan yes, as I mentioned the query produced using the Eloquent whereRelation approach will be unlike a regular SQL JOIN. Generally, for your use case, I would also join the tables rather that the whereRelation or whereHas alternative.

Mark the thread closed if you're all set.

cemilakan

2 years ago

Level 1

@tykus i dont know how to mark the thread closed :D i am new here couln't fint that

Please or to participate in this conversation.