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?

Are you trying to query one user's roles or many users' roles? — Kemal Fadillah, Dec 24 '13 at 11:17
Duplicate of: http://stackoverflow.com/questions/18538527/how-to-filter-a-pivot-table-using-eloquent — Arda, Oct 23 '14 at 20:25

score 19 · Answer 1 · answered Dec 24 '13 at 11:47

19

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.

answered Dec 24 '13 at 11:47

Dkok

213
1
5

Thanks for the reply. I like your solution, however I'm on Laravel 4.0, so I can't use wherePivot. – user1544110 Dec 24 '13 at 13:59
1

@user1544110 thanks! For Laravel 4.0 you can use a where() instead of the wherePivot() – Dkok Dec 24 '13 at 16:07
but if you use where instead of wherePivot then the query would check if table roles has a column primary? – Adam Oct 14 '17 at 23:26

score 16 · Accepted Answer · answered Dec 24 '13 at 11:45

16

Try the following:

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

answered Dec 24 '13 at 11:45

Anam

11,999
9
49
63

Thanks, I'm going to use this solution. – user1544110 Dec 24 '13 at 13:58
WARNING: I tried this in Laravel 7.x (although not precisely analysed) and this query also returned users where the column should not be matched!, Something's weird here : the actual SQL query constructed and executed by laravel ends with: "... where pivot_table.user_id in (1, 2, 3, 4, 5, 6, 7) and pivot_extra_column = 1" so it actually returns all 7 users (I do not know what for), even those that do not have pivot_extra_column = 1. So this is weird! Perhaps a bug? – Tomeg Apr 07 '20 at 14:27

Filtering pivot table data with Laravel models

2 Answers2

Linked