CODEWITHSUNDEEP
phplaravelormeloquentquery-builder
Hamed Adil
Hamed Adil

Reputation: 483

Laravel querying multiple related models

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.

Upvotes: 0

Views: 1524

Answers (2)

Eric Tucker
Eric Tucker

Reputation: 6335

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();

Upvotes: 4

Sandeesh
Sandeesh

Reputation: 11906

Assuming the User model has a relationship profile and the Profile model has a relationship interests, you can do this.

$interest_id = 1;

$users = User::whereHas('profile', function ($query) use ($interest_id) {
    $query->whereHas('interests', function ($query) use ($interest_id) {
        $query->where('id', $interest_id);
    });
})->get();

Upvotes: 1

Related Questions