Laravel查询多个相关模型

Question

For example: I have these models in my application. User, Profile, Interest.

I linked the users table with the profiles table by adding the user_id column in the profiles table. And I linked profiles and interests by using a pivot table (interest_profile), Which is (as obvious) will have two columns (profile_id, interest_id).

However, I want to query the users who are associated with a profile, too see who is associated with a particular interest, In other words: "select all users who are having (in their profiles) that particular interest".

I know that I can do this with raw SQL by joining the four tables and then use (where clause).. But I want to do it the Laravel way.

Thanks in advance.

Answer 1

First make sure you have your relationships setup correctly on your models like:

class User extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
}

class Profile extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function interests()
    {
        return $this->belongsToMany(Interest::class, 'interest_profile');
    }
}

class Interest extends Model
{
    public function profiles()
    {
        return $this->belongsToMany(Profile::class, 'interest_profile');
    }
}

Then you can use whereHas() to constrain a query by a related model and dot notation for nested relations. So your query would be:

User::whereHas('profile.interests', function($query) use ($interestName) {
    return $query->where('name', $interestName);
})->get();

That would just return a collection of users. If you wanted to return their profiles and interests as well you would use with():

User::whereHas('profile.interests', function($query) use ($interestName) {
    return $query->where('name', $interestName);
})
->with('profile.interests')
->get();