Spatie Laravel Permissions - How To Get Users that have one Role or another Role

Question

I'm trying to figure out if there is a simple way to get all the users that have a role or another role.
On the official documentation of Spatie Laravel Permissions, I couldn't find this.

I'm trying like this but I get all the users.

User::role(['Individual', 'Organisation', 'Venue'])->get();

I'm also trying like this:

User::whereHas("roles", function($q) {
                $q->orWhere(function ($query) {
                    $query->where("name", "Individual")
                        ->where("name", "Venue")
                        ->where("name", "Organisation");
                });
            })->get();

In the Db roles table I have:

swap the `orWhere` to the inner query (3 times) rather than the outer one — apokryfos, Nov 03 '20 at 08:12
https://github.com/spatie/laravel-permission/blob/master/src/Models/Role.php#L61 The Role model has a "users" method. Just use `Role::findByName()` and then `$role->users` — stokoe0990, Nov 03 '20 at 09:25
I apologize, I found out that the first query I was using works properly, just I was not ordering the data I was getting and I could not see at the end the user that I expect to see. — Davide Casiraghi, Nov 03 '20 at 10:14

Kamlesh Paul · Answer 1 · 2020-11-03T09:26:49.043

5

you can try whereIn() to roles() ref link https://laravel.com/docs/8.x/queries#additional-where-clauses

User::with("roles")->whereHas("roles", function($q) {
    $q->whereIn("name", ["Individual","Venue",'Organisation']);
})->get();

try id for better search as it is primary key

User::with("roles")->whereHas("roles", function($q) {
    $q->whereIn("id", [6,7,8]);
})->get();

NOTE here name should match exact string of roles name which in your database

edited Nov 03 '20 at 09:26

answered Nov 03 '20 at 08:25

Kamlesh Paul

11,778
2
20
33

Thank you for answering. Also in this case I don't get the users that are just organization. – Davide Casiraghi Nov 03 '20 at 08:26
If I take your code and I remove "Individual","Venue" I get the users that are Organisation.. So the string is correct – Davide Casiraghi Nov 03 '20 at 08:29
same problem using the id – Davide Casiraghi Nov 03 '20 at 08:33
for just organisation use where() directly no need to use whereIn, `$q->where("id", 5);` – bhucho Nov 03 '20 at 09:04
@DavideCasiraghi try `with("roles")` then you will able to see is it correct result or not – Kamlesh Paul Nov 03 '20 at 09:27

score 3 · Answer 2 · answered Nov 03 '20 at 08:21

3

@Apokryfos meant you to change it like this

User::whereHas("roles", function($q) {
                $q->where("name", "Individual")
                    ->orWhere("name", "Venue")
                    ->orWhere("name", "Organisation");
            })->get();

if you instead want the users to have all roles and not one of the roles

User::whereHas("roles", function($q) {
        $q->where("name", "Individual")
    })->whereHas("roles", function($q) {
        $q->where("name", "Venue")
    })->whereHas("roles", function($q) {
        $q->where("name", "Organisation")
    })->get();

answered Nov 03 '20 at 08:21

N69S

16,110
3
22
36

I tried like this but I don't' get the users that are just Organization – Davide Casiraghi Nov 03 '20 at 08:24
@DavideCasiraghi did the second one fail too ? – N69S Nov 03 '20 at 08:25
I need just the first one.. I need an OR, not and AND. – Davide Casiraghi Nov 03 '20 at 08:27

score 2 · Answer 3 · edited Mar 17 '23 at 05:51

2

Get all users who have roles assigned

$users_with_roles = User::has('roles')->get();

Get all users who do not have roles assigned

$users_without_any_roles = User::doesntHave('roles')->get();

edited Mar 17 '23 at 05:51

Timothy

2,004
3
23
29

answered Mar 10 '23 at 08:45

Jay Prakash Jain

21
3

Spatie Laravel Permissions - How To Get Users that have one Role or another Role

3 Answers3