Laravel eloquent - Many to Many, select only the appropriate table

Question

I have 3 tables describing users, roles and role_user. They look like this:

users -> id, name roles -> id, name role_user -> user_id, role_id 

In my User class, I have:

 public function roles() { return $this->belongsToMany('Role'); } 

The goal here is, of course, to allow the user to have multiple roles.

I can execute a query like User::with('roles')->get() , which works fine.

However, I only want to select users who have a specific role, ideally given by name, but if necessary it can be by identifier. How to do it with Eloquent?

Answer 1

Write belongsToMany in Role Model

  class Role extends Eloquent { public function users() { $this->belongsToMany('Users'); } } 

Then use the following Eloquent Query.

  $role = Role::with('users')->whereName($name)->first(); 

You can access all users with $name role as $role->users .

Answer 2

Tharumax answer is fully valid and extremely helpful. The only problem is that you want to run the request with other things. Say users who have the admin role and are registered before 2014. Or if you want to use it as a scope, you cannot do it your own way.

This is another way, but directly by request (although this search is performed by id):

 User::join('role_user', 'users.id', '=', 'role_user.user_id') ->where('role_user.role_id',$id)->get(); 

If you want, you can create an area to simplify syntax and management. for example, I want to get users with the admin role.

Application area:

 // Inside User model public function scopeAdmins($query) { return $query->join('rol_user', 'users.id', '=', 'rol_user.user_id') ->where('rol_user.rol_id', id_of_admin ); } 

Then you can easily get all the admin users who do this (and, if you need to, add other conditions):

 User::admins()->get();