Laravel eloquent - Many to Many, select only matching the many table

Question

I have 3 tables describing users, roles and role_user. These 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 aim here is of course to allow a user to have multiple roles.

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

What I want though is only to select users that have a certain role, ideally specified by name, but it could be by ID instead if needed. How do I do this using Eloquent?

Answer 1

Write a belongsToMany relationship in Role Model

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

Then use Following Eloquent Query.

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

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

Answer 2

The answer by tharumax is completely valid and extremely useful. The only problem is if you want to run the query with other things. Let's say, the users that have an admin role and signed up before 2014. Or if you want to use it as a scope, you can't do it his way.

This is another way but directly on the query (though this one searches by id):

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

If you want, you could create a scope for easier syntax and management. e.g I want to get the users with role admin.

Scope:

// 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 could easily retrieve all the admin users doing this (and add other conditions if you need to):

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