CODEWITHSUNDEEP
laraveleloquent
DatsunBing
DatsunBing

Reputation: 9076

Eloquent: How to combine eager loading and filter by pivot?

I have a many-to-many relationship between models. For a given model instance, I know how to filter the related models according to the value of a pivot. For example, in the case of users and roles, I would use:

User->roles()->wherePivot('admin',1);

Similarly, I know how to eager load all roles for a set of users:

User::where('active',1)->with('roles')->get();

What I am trying to do is combine these two concepts. For example, how do I retrieve a set of users with their eagerly loaded roles, where the users are filtered according to a field on the pivot?

I realise I can do this easily enough with raw SQL, but I would prefer to use Eloquent if possible.

Upvotes: 2

Views: 1556

Answers (2)

user320487
user320487

Reputation:

Pass a closure in to with when eager loading to add clauses to the query: 

        User::with(['roles' => function ($query) {
            $query->where('admin', 1);
        }])->where('active', 1)->get();

Upvotes: 1

fubar
fubar

Reputation: 17378

You can restrict your eager loaded relations using a closure.

$users = User::with(['roles' => function ($query) {
        return $query->wherePivot('admin', 1);
    }])
    ->where('active', 1)
    ->get();

You can also query relations using whereHas.

$users = User::with('roles')
    ->whereHas('roles', function ($query) {
        return $query->wherePivot('admin', 1);
    })
    ->where('active', 1)
    ->get();

Upvotes: 5

Related Questions