CODEWITHSUNDEEP
mysqlsqllaravellaravel-permission
nullifier
nullifier

Reputation: 23

Spatie Laravel Permissions get users with permissions

I'm trying to figure out if there is a simple way to get all the users that have role with given permissions. So i used permissions through roles it means that i don't have direct relation to them so i need to check relation of user model "roles" and then check permissions that associated to each of user role.

SqlFiddle with set of data - http://sqlfiddle.com/#!9/2fe35d

Task is to get users that have permissions with id 2 and 3 for example.

Upvotes: 0

Views: 5038

Answers (4)

Peppermintology
Peppermintology

Reputation: 10220

The Spatie permissions package uses Laravels native authorization functionality meaning you have access to a lot of the features defined in the authorization docs.

One of these features is the can() directive. You can pass the name of a permission to can() and it will return true or false based on the check. For example:

@can('view posts')

if (auth()->user()->can('view posts') { }

if ($user->can('view posts') { }

Using your example, if you want to find all users that have any of your permissions:

// Get the name of your permissions
$permissions = Permission::whereIn('id', [2,3])->pluck('name')->toArray();

// Filter users based on if they have any of the permissions
$users = User::all()
    ->filter(function ($user) use ($permissions) {
        if ($user->can($permissions)) {
            return $user;
        }
        return false;
    });

Alternatively if the users must have all the permissions:

// Filter users based on if they have all of the permissions
$users = User::all()
    ->filter(function ($user) use ($permissions) {
        foreach ($permissions as $permission) {
            if (!$user->can($permission)) {
                return false;
            }
        }

        return $user;
    });

Check out the docs for using permissions via roles on the Spatie website.

Upvotes: 0

Remul
Remul

Reputation: 8252

I did not find it mentioned in the documentation but there is a permission scope that should give you all users that either have the permission directly or through a role:

public function scopePermission(Builder $query, $permissions): Builder

You can use it like this:

User::permission([2, 3])->get();

Upvotes: 1

eshirvana
eshirvana

Reputation: 24593

If you only need list of users you can use query below:

select distinct u.id , u.name username
from users u
join user_has_role ur
   on u.id = ur.user_id
join role_has_permissions rp
   on ur.role_id = rp.role_id
where  rp.permission_id in ( 2, 3)

Upvotes: 2

JoshuaG
JoshuaG

Reputation: 400

Please check the following query, thanks

select U.id, U.name AS UserName, R.name AS RoleName,P.id As PermissionID, P.name AS PermissionName
FROM users U 
INNER JOIN user_has_role ur on U.id = ur.user_id
INNER JOIN roles R on ur.role_id = R.id
INNER JOIN role_has_permissions rp on R.id = rp.role_id
INNER JOIN permissions P on rp.Permission_id = P.id
WHERE P.id in (2,3)

Upvotes: 1

Related Questions