Writing a query to find many-many records without corresponding record in another many-many table

Question

I seek help for writing a query.

Some context: I have the ff models

User(users) - id, name
Post (posts) - id, content
Bookmarks - user_id, post_id
Comment( comments) - id, content, post_id
Like(likes) - user_id, comment_id

I want to see which users have liked any comment in a post without bookmarking that particular post.

Currently i write the query like this, but it return empty result.

Like::leftJoin('comments', 'likes.comment_id', '=', 'comments.id')
    ->leftJoin('bookmarks', 'bookmarks.post_id', '=', 'comments.post_id')
    ->whereNull('bookmarks.post_id')
    ->pluck('likes.user_id')

select likes.user_id from likes 
left join bookmarks on bookmarks.post_id = comments.post_id
left join comments on likes.comment_id = comments.id
where bookmarks.post_id is null
 

I expect it to return list of user_id without corresponding bookmarks. It returns an empty list instead.

Answer 1

I was unable to replicate the change in join order that your question suggests. You are also missing the join criterion for user_id between likes and bookmarks. I think it should be:

Like::join('comments', 'likes.comment_id', '=', 'comments.id')
    ->leftJoin('bookmarks', function ($join) {
        $join->on('bookmarks.post_id', '=', 'comments.post_id');
        $join->on('bookmarks.user_id', '=', 'likes.user_id');
    })
    ->whereNull('bookmarks.post_id')
    ->distinct()
    ->pluck('likes.user_id');