Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

lonerunner's avatar

laravel retrieve all users where value match pivot table

So im trying to make relationship between user roles and profiles everything works connected on creation but i don't know how to filter out when i want to list users by specific role or profile.

Here is a table structure that i have now. Each table corresponds to it's model, so table users have model User table roles have model Role and there is a pivot table role_user that connects users with roles.

This would be role table

+----+----------+---------------+
| id |   name   |  Description  |
+----+----------+---------------+
|  1 | admin    | Administrator |
|  2 | client   | Client        |
|  3 | operator | Operator      |
+----+----------+---------------+

This would be pivot table for user role

+----+---------+---------+
| id | user_id | role_id |
+----+---------+---------+
|  1 |       1 |       3 |
|  2 |       2 |       1 |
+----+---------+---------+

And this would be user table normally

+----+--------+-----------------+
| id |  name  |      Email      |
+----+--------+-----------------+
|  1 | Mario  | [email protected] |
|  2 | Luighi | [email protected]   |
+----+--------+-----------------+

So if i would list all account that is very simple, as i would just use

public function index(Request $request)
{

  $users = User::get();

  return view('users/index', compact('users'));
}

If i want to filter users by specific name or email i would just filter

$users = User::where('name', 'Mario')->get();

That would retrieve all users where matching name is mario.

Now something more complex is where i stumbled upon and i couldn't quite find answers on internet.

I want to retrieve all users where user role from pivot table matches role from role table.

Example would be

Retrieve All Users WHERE (pivot table) role_user EQUALS name OPERATOR from role table

That would return user Mario, as his user id is 1 and in pivot table that user_id matches role_id 3 which in roles table is role named OPERATOR.

0 likes
5 replies
staudenmeir's avatar

Have you defined a roles relationship in the User model?

1 like
Ricardo's avatar
Ricardo
Best Answer
Level 48

@lonerunner if in you have a roles relation on user :

$users = App\User::whereHas('roles', function($query) {
        $query->where('id', 3);
    })
    ->get();

You get the idea.

2 likes
lonerunner's avatar

I think i got relationships right.

One User can have many roles and one profile so User model:

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

    public function profile() {
      return $this->hasOne(Profile::class);
    }

Role can have many users but roles belongs to users so Role model:

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

And also Profile belongs to user so Profile model

    public function user() {
      return $this->belongsTo(User::class);
    }

And actually @Ricardo solution is what it works, i also tried to add filter for profiles too into the mix and it works. It's documented on laravel website, but on search results i never got anything related to whereHas

1 like
Jahchap's avatar

@lonerunner this was driving me crazy as well. Just as you pointed out, your relationships are right and while @Ricardo's answer works, there is a rather subtle solution (subtle because it talks about one user's roles while here it is about one role with its users) on the Laravel relationships page and it is shorter and better.

For anyone looking for how, this should do it:

$users = App\Role::find($role_id)->users;

Or using the name of the role:

$users = App\Role::where('name', 'client')->first()->users;

This will give you an array of all the users that belong to that role.

1 like
mughalseohub's avatar

@Jahchap This works but it returns all user details. Can I only get specific user columns not the password and stuff.

Please or to participate in this conversation.