Can i use where condtiton after calling relation with @with method?-CodePudding

This code:

$users = Users::with(['posts' => function($post) use ($request) {
    $post->where('votes', '>', 100);
}])->get();

dd($users->toArray());

Return output like this:

0 => [
    "user_id" => 1
    "posts" => []
]

What can I do to return an empty array of users? I tried to do like this:

$users = Users::with(['posts'])
    ->where('posts.votes', '>', 100)->get();

dd($users->toArray());

But got an error:

Undefined table: 7 ERROR: issing FROM-clause entry for table "posts"

CodePudding user response：

With the syntax you tried, the answer is "No", as ->with() doesn't perform any kind of join logic; so posts tables is not available there.

If you want to filter the User records returned based on a condition of a relationship, you need to use one of has() or whereHas() method. In your case, since you're looking for votes > 100, with votes being a column, you need to use whereHas():

$users = Users::whereHas(['posts' => function($query) {
    return $query->where('votes', '>', 100);
}])->get();

This will limit the User record being returned to those that have 100 votes or more.

Additionally, you can use both whereHas() and with() with the same function to Filter and Eager Load the relationship (preventing extra queries from being run later). Syntax for that would be:

$closure = function ($query) {
  return $query->where('votes', '>', 100);
});

$users = Users::whereHas(['posts' => $closure]
->with(['posts' => $closure])
->get();

Complete documentation is available here:

https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence

CodePudding user response：

I think you need to use whereHas method. Can you try like this?

$users = Users::with(['posts'])
              ->whereHas('posts.votes', function($q){
                     $q->where('votes', '>', 100);
              })->get();

dd(optional($users)->toArray(), $users->toArray());