How do I get all Users with Roles where role = 'admin' using eloquent along with Entrust?

$users = User::with(['roles' => function($q){
    $q->where('name', 'admin');
}])->get();

@RachidLaasri

That didn't work.It fetches all the Users including Users with superadmin role.

$users = User::whereHas('roles' => function($q){
    $q->where('name', 'admin');
})->get();

What does the users table look like? Your query should be something like:

$adminUsers = User::with('roles')->where('role', 'admin')->get();

@ashwebb

users id,name,email,password

roles, id,name

role_user id,user_id

@laraDev98 Why do you need to have it be a many-to-many relationship? Would you ever want one user to have more than one role?

I'm using this Package https://github.com/Zizaco/entrust.It also provides route-protection,permissions.

Ok I haven't ever used entrust, but I would try this if I were you.

$users = DB::table('users')->with('roles')->join('roles', 'roles.user_id', '=', 'users.id')->where('roles.name', 'admin')->get();

If you only want the name and role you can add a select, like this:

$users = DB::table('users')->select('users.name as username', 'role.name as role')->with('roles')->join('roles', 'roles.user_id', '=', 'users.id')->where('roles.name', 'admin')->get();

I know that but I want a cleaner way to fetch it.This looks like CI stuff.

Ah, well that is my recommendation. It is a very explicit query, which is good practice. You certainly shouldn't feel any need to shy away from such a query. But if you want to run it a a different way, I'll wish you good luck.

ETA: I reread your initial question, and this isn't a raw query.

Thanks for your help.Hope some posts the correct way

@ashwebb many cases where a user can have multiple roles. Example is forums where a user has a primary role that has basic permissions and a display roll with specific permissions for a particular forum.

Answer to @laraDev98. Run the inverse of the query. Role::with('users')->where('name', 'admin')->get();

@jekinney Ah, thanks. Sometimes you just need a different perspective.

@laraDev98

Depends on what your result is. For instance you want to list all the users and show which role they have then query the users table as primary.

If you want to display all the roles with associated users, the the role table will be primary.

As far as querying the user table for certain roles isn't an efficient way as generally you'll have a lot more users for your query to cycle through to find the needle in the hay stack. So it can be done, but you should have a good reason for that arguably more expensive query. For entrust I believe you need: User::hasRole('admin')->get();

Just re-read your last question. Not a rule but it is about utilizing the relationship and efficient queries. Many to many can obviously go either way just fine, when you have many to one a particular schema as mentioned is just to be quick and efficient. Technically speaking it doesn't matter per say which is primary table. But sometimes as in your case, you need to get all users, then loop through each user and loops through the pivot table checking each id to the given role name and getting only the user row with the role. Where the inverse calls a particular role and loops through the pivot table gathering each user id and subsequent user row.

In this case because your comparing a string in roles table to fetch a role id, then looking at the pivot table to get each user id, then the user table to get each row.

@jekinney

Thank you so much for the detailed explanation.Thank you!!

I don't know if you still have this problem, but I had the same problem and I did it this way:

Role::where('name', 'admin')->first()->users()->get()

So what it does. First get the Role model object that has a name of "admin" and then get the related users, using the belongsToMany relationship from Laravel.

Maybe this way works better for you.

I realize this is an old thread and my answer is unrelated to Entrust but this works with Laravel's UserHasRoles:

$admins = User::whereHas('roles', function($q){$q->whereIn('role_name', ['administrator']);})->get();

HTH

@laraDev98 sorry if this is a long, long looooong delay. I tested some options and for the zizaco I use this approach based on @RachidLaasri

$users = User::whereHas('roles', function($q){
    $q->where('name', 'admin');
})->get();

 $users = User::whereHas('roles', function ($q) {
       //conditions from role table
            $q->Where('name', 'admin')->
                orWhere('name', 'physician');

        })->
           // conditions from Usertable
          where('active_status', 1)->paginate(20);

I know you got your answer, may be another guys use it.

This is the answer

User::whereHas('roles', function ($query) {
    $query->where('id', 1);
});

Hi I know this is very late but it may help someone to get users list.

There are two ways as:

1. $admins=User::whereHas('roles', function($q){$q->where('name', 'admin');})->get();
2. $admins=User::whereHas('roles', function($q){$q->whereIn('roles.name', ['admin']);})->get();

I would like to say like this....

$admins = User::whereHas('roles', function($q){$q->whereIn('roles.name', ['admin']);})->get();

dd($admins);

And i got my result.....

@helvetian answer worked for me in laravel 5.5

$clients = User::whereHas('roles', function($q){$q->whereIn('name', ['client']);})->get();

User::whereHas('roles', function($q) {
        $q->whereName('insert_name_of_role');
    })->get();

This gives you all the users with the certain role you're looking for.