Level 52
Try something like that :
$users = User::whereHas('roles', function ($q) {
$q->whereNotIn('roles.id', Role::where('id', '!=', 1)->lists('id'));
})->get();
Jun 23, 2015
7
Level 1
ManyToMany whereNotIn
Hello,
i have 3 tables:
users:
| id | group_id |
| --- | -------- |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
| 7 | 2 |
user_role:
| user_id | role_id |
| ------- | ------- |
| 1 | 1 |
| 2 | 3 |
| 4 | 2 |
roles:
| id | group_id |
| --- | -------- |
| 1 | 1 |
| 2 | 3 |
| 3 | 1 |
and now i want for example all users with group_id 1 who have no assignment to any group of group_id = 1
in my example, i want get the user with id = 3 and user with id = 4, cause they are not in role = 1 or role = 3.
I hope anyone understand me an could help me with this query?
Level 1
@bestmomo thank u,
but this is not the correct one. I want get all missing Users for each Group.
- witch user of group 1 has no role
- witch user of group 2 has no role ....
Level 56
$users = User::where('group_id', '=', $group_id)->has('roles', '=', 0)->get();
Roles in the has is the name of the function defining the belongsToMany relationship with roles.
Check http://laravel.com/docs/5.0/eloquent#querying-relations
Level 1
@pmall can u help me again?
if i want edit for example role = 1, i want get all missing users from the group = 1 and all users that have role = 1
so my result must be: User = 1 (has role 1, has group 1), User = 3 (has no role, has group 1)
User = 2 should be no part of my result, cause he has role = 3
Level 56
Explain with more details what do you want to do on a higher level. All this seems strange to me.
Level 1
@pmall Sorry, i'm not so good to explain it, but i try:
In my Database, i have a lot of groups. Every user can just do something in his own group.
Every group can have a lot of roles. But every user can just have one Role.
If i create a new Role, i want get all Users from the Group who have no Role. that was my question from yesterday. The answer from u work great.
Now i want to edit one role: If i edit the role, i want a checkboxlist with all Users of this Role (checked=true) and all users who has no Role. I want do that to uncheck some users and to add new users. But all these only in the given group.
I hope this description is better
Level 56
I think you should try to simplify your interface. It is a very strange way to manage things.
For example just put checkboxes for all users in the group and disable them when rendering the view if it already has another role. Or just don't display them. Avoid complex requests like this.
Please or to participate in this conversation.