Many To Many Query - Eloquent

Question

I have a user table, a role table and a role_user table.

A user has many roles.

A role belongs to many users.

Relationships have been defined on each model.

I need to find out if a specific user has a role(s). Role(s) could be a string or an array of roles.

For starters I've tried just looking for one role title.

I've tried:

  Role::with(array('users' => function($query){

      $query->where('title', '=', 'Admin');

  }))->get();

But no luck. How can I search for one specific user id?

Your code looks correct. Is "admin" the title of a user or role? What exactly isn't working? Have you examined the query log? Take a look at DB::getQueryLog(). If you run the queries directly in SQL, what happens? Also take a look at has(), as you'll probably want to use that to return only roles that have users, and vice versa. — Dave
Admin is the title of a user role. The above code does work, but how can I customise it, so that I can search for multiple roles, eg. Admin/Public/Whatever and how can I limit the search to a specific user? — panthro

Dave Dave · Accepted Answer · 2014-08-14T15:28:30

If "admin" is the name of a role, then your query is wrong.

Role::with(array('users' => function($query){
  // Use this area to filter users, NOT roles
}))->get();

You want something like this.

// Role "admin" and all the users with that role
// roles.name = 'admin'
Role::has('users')->with('users')->where('name', 'admin')->get();

To filter users or roles you simply use Eloquent as you normally would.

// All users with the role named "admin"
User::has('roles')->with(array('roles' => function($q) {
    // roles.name = 'admin'
    $q->where('name', 'admin'); 
}))->get();

// User named "foo" with the role named "admin" or "public"
User::has('roles')->with(array('roles' => function($q) {
    // roles.name IN ('admin', 'public')
    $q->whereIn('name', array('admin', 'public'));  
}))->where('name', 'foo')->get(); // users.name = 'foo'

Simply flip everything around to query Roles with Users.

Many To Many Query - Eloquent

1 Answers