Users that have role of supervisor and teaches IT

I have 3 tables users, roles and fields.

The relations between users and fields are stored in pivot table field_user while the users and their roles are stored is role_user.

User class

public function roles() { return $this->belongsToMany(Role::class); }

public function fields()
{
    return $this->belongsToOne(Field::class);
}

Field class

public function users() { return $this->belongsToMany(User::class); }

Role class public function users() { return $this->belongsToMany(User::class); }

How to get all users that have role supervisors and belong to the IT field.

I managed to get all supervisors with

$supervisors = User::whereHas('roles', function ($query) use($role_name) {

    $query->where('name', $role_name);

    })->get();

but I cannot figure out how to filer only those with IT field

jlrdw

6 years ago

Level 75

If you search medium they have some decent articles on this subject.

Sinnbeck

6 years ago

Best Answer

Level 102

How about two where has?

$supervisors = User::whereHas('roles', function ($query) use($role_name) {

    $query->where('name', $role_name);

    })->whereHas('fields', function ($query) use($field_name) {

    $query->where('name', $field_name);

    })->get();

tnort

6 years ago

Level 4

I got an error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.fields_id' in 'where clause' (SQL: select * from users where exists (select * from roles inner join role_user on roles.id = role_user.role_id where users.id = role_user.user_id and name = supervisor) and exists (select * from fields where users.fields_id = fields.id and name = IT))