Search code examples
mysqlsqllaravellaravel-permission

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.

Solution

  • 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)