Filtering pivot table data with Laravel models

Question

Let's say I have three tables (this is just an example):

users
   user_id
   username

roles
   role_id
   name

user_roles
    user_id
    role_id
    primary (boolean)

And the corresponding laravel models:

   class User extends Eloquent {
        public function roles() {
              return $this->belongsToMany('Role')->withPivot('primary');
        }
   }
   class Role extends Eloquent {
         public function users() {
              return $this->belongsToMany('User')->withPivot('primary');
         }
   }

I want to get a list of all users, but with only the primary roles in the returned object. If I use something like:

$users = User::with('roles')->find(1);

Each user object will have a list of all the roles corresponding to it. I want this list to contain only the primary roles. Is there any way to do this from a query, without post processing the $users array?

Answer 1

I'd suggest you create an extra method in your User model like this:

public function primaryRoles() {
    return $this->roles()->wherePivot('primary', true);
}

Then use something like:

$users = User::with('primaryRoles')->find(1);

Also, the "Eager Load Constraints" section in the documentation might be relevant.

Answer 2

Try the following:

$users = User::with(array('roles' => function($query)
{
    $query->where('primary', 1); 
}))->find(1);