laravel eloquent with and join conflict

Question

I am quite new to Laravel... I have a posts table that has a relation with articles and users tables. In the controller I either get all the posts matching a search criteria or get all posts. In both cases, I learned how to add the related fields to the collection $posts.

// get all posts matching criteria
if (request('search') && request('search') != '') {
    $posts = Post::orderBy($orderBySortField,$sortOrder)
        ->with(['user:id,name','article:id,title'])
        ->where('post','like','%'.request('search').'%')
        ->join('users', 'posts.user_id', '=', 'users.id')
        ->orWhere('users.name','like','%'.request('search').'%')
        ->join('articles', 'posts.article_id', '=', 'articles.id')
        ->orWhere('articles.title','like','%'.request('search').'%')
        ->get();
// get all posts
} else {
    $posts = Post::orderBy($orderBySortField,$sortOrder)
        ->with(['user:id,name','article:id,title'])
        ->get(); 
}

// add related fields
foreach ($posts as $post) {
    $post->title = '['.$post->article->title.']';
    $post->user = '['.$post->user->name.']';
}

When I get all the posts, the related fields are correct in the list displayed in the view page.

However, if I search for the name of a specific user, I get a list where the $posts->user are incorrectly related.

I could figured that by displaying the last queries.

For all the posts:

select * from `posts` order by `posts`.`id` desc
select `id`, `name` from `users` where `users`.`id` in (1, 11)
select `id`, `title` from `articles` where `articles`.`id` in (1)

For the posts written by user 'paul' (user_id = 1):

select * from `posts` inner join `users` on `posts`.`user_id` = `users`.`id` inner join `articles` on `posts`.`article_id` = `articles`.`id` where `post` like ? or `users`.`name` like ? or `articles`.`title` like ? order by `posts`.`id` desc [where ? = %paul% %paul% %paul% ]
select `id`, `name` from `users` where `users`.`id` in (11)
select `id`, `title` from `articles` where `articles`.`id` in (1)

So why is Laravel last queries look for user_id = 11 which is another user?

Am I doing something wrong?

N69S N69S · Accepted Answer · 2021-03-12T10:28:15

Change the join code the whereHas

// get all posts matching criteria
if (request('search') && request('search') != '') {
    $search = request('search');
    $posts = Post::orderBy($orderBySortField,$sortOrder)
        ->with(['user:id,name','article:id,title'])
        ->where('post','like','%'.$search.'%')
        ->orWhereHas('user', function($userQuery) use($search){
            $userQuery->Where('users.name','like','%'.$search.'%');
        })
        ->orWhereHas('article', function($articleQuery) use($search){
            $articleQuery->Where('articles.title','like','%'.$search.'%');
        })
        ->get();
}

laravel eloquent with and join conflict

2 Answers