eloquent: query many to many relationship

Question

I create many to many relationship between users and roles like this:

User Model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The roles that belong to the user.
     */
    public function roles()
    {
        return $this->belongsToMany('App\Role');
    }
}

Role model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Role extends Model
{
    /**
     * The users that belong to the role.
     */
    public function users()
    {
        return $this->belongsToMany('App\User');
    }
}

my question is how I can get only users that belongs to some specific role name, for example if I have a role name called "author", I want to get all author users, something like this in sql:

SELECT users.id, users.name 
FROM users, roles, role_user 
WHERE users.id = role_user.user_id
AND role_user.role_id = roles.id
AND roles.name="author";

note: I'm using Laravel 5.4

Answer 1

You should try this:

Role::with(array('user'=>function($query){
        $query->select('id','name');
    }))
   ->where('role_name', 'author')
   ->get();

Answer 2

You can try this following code:

$userRoles = Role::where('name', 'author')->with('users')->get();
$userRoles->users->id;
$userRoles->users->name;

or

$userRoles = Role::select('users.id, users.name')->where('name', 'author')->with('users')->get();

Answer 3

User::whereHas('roles', function($query){
   $query->where('role_name', 'author')
})
->get(['name', 'id']);

You may change column name for table role, I have used role_name,

Now this should work, it will retrieve name and id for users only with at least one role