如何通过匹配关系中的值来获取Laravel模型？

Question

I have a User model with attributes like login, ID, age, etc. Another table is user_data with a residence column, for example.

How can I get all users with a specific residence? I have this:

User model:

public function user_data()
{
    return $this->hasMany('App\Models\UserData');
}

public function FilterUser($request)
{
    if ($request->has('city')) 
    {
        $users = User::with('user_data')->where('residence', 'Warsaw')->get();        
    }

    dd($users);
}

UserData model:

public function user()
{
    return $this->belongsTo('App\User', 'user_id');
}

Now I get this error:

Column not found: 1054 Unknown column 'residence' in 'where clause' (SQL: select * from `users` where `residence` = warsaw and `users`.`deleted_at` is null)

And another question:

If User is my main model, should I connect it with relationships like above? hasMany() or belongsTo()?

Answer 1

Use the whereHas() query builder method:

$users = User::with('user_data') 
    ->whereHas('user_data', function($query) { 
        $query->where('residence', 'Warsaw'); 
    })
    ->get();

Edit - To use the value from $request you need to import the variable to the closure for whereHas():

    ...
    ->whereHas('user_data', function($query) use ($request) {
        $query->where('residence', $request->city);
    })
    ...

Edit - A query scope may be a better approach. In the User model, add this method:

function scopeInCity($query, $city)
{
    return $query->whereHas('user_data', function($q) use ($city) {
        $q->where('residence', $city);
    });
}

Then you can filter all users by city (outside the model):

User::inCity($request->city)->get();