0

How do I get only users from database where they have no role assigned? User belongs to many roles, the exact relation is:

public function roles() {
    return $this->belongsToMany(Role::class, 'user_has_roles', 'user_id', 'role_id');
}

My problem is, that I do only work with Query builder, not with the model so I do not want to use relation.

EDIT:

I am working with migrations, so please no solutions when using model.

Epsilon47
  • 768
  • 1
  • 13
  • 28

1 Answers1

1

You can use doesntHave, I believe this uses QueryBuilder only.

User::doesntHave('roles')->get();

Reference: https://laravel.com/docs/5.6/eloquent-relationships

UPDATE

Using raw SQL query, you can do this.

Basically what this tries to do is to get all users where its id is not found in the user_has_roles table (meaning it doesn't have any relations)

SELECT  *
FROM    users
WHERE   id NOT IN (SELECT user_id FROM user_has_roles)

I think you can convert this into a JOIN or something. I'm not that expert in raw SQL though.

UPDATE Trying Query Builder

$usersWithoutRoles = DB::table('users')
        ->whereNotIn(function ($query) {
            $query->select(DB::raw('user_id'))
                  ->from('user_has_roles')
                  ->whereRaw('user_has_roles.user_id = users.id');
        })
        ->get();

Reference: SQL - find records from one table which don't exist in another

You can try other alternatives there.

Wreigh
  • 3,215
  • 16
  • 37
  • Thanks for advice, but I am working with migrations I cannot use model. – Epsilon47 Feb 20 '18 at 07:09
  • well in that case, you have to create a native query – Wreigh Feb 20 '18 at 08:14
  • I've updated my answer, note that it only *gets*, it doesn't insert (since you didn't include that in your question). And I believe it can be optimized further. – Wreigh Feb 20 '18 at 08:33
  • Oh I forgot about my question, what I really asked for and I posted the whole problem in the answer, sorry about that. Your code, anyways, is what I needed, thanks :) – Epsilon47 Feb 20 '18 at 08:35