Laravel 4 Eloquent table join and where clause on multiple tables

1
votes

Hi I'm using Laravel and Eloquent in order to store users, there profile and there assigned roles. My table structure is as follows;

users

id | username | email | password | created_at | updated_at

profiles

id | user_id | firstname | created_at | updated_at 

roles 

id | name 

assigned_roles 

id | user_id | role_id

I'm trying to select all users where there role name is equal to admin. But when I do the following I get an empty array:

 return \User::with('profile')->join('assigned_roles', function ($join) {
        $join->where('assigned_roles.user_id', '=', 'users.id');
    })->get();

Any ideas what I'm doing wrong?

Also I am using Zizaco Entrust and Confide https://github.com/Zizaco/entrust/tree/1.0

1
phplaraveljoinlaravel-4eloquent
@trincot no that is correct. i have a function in app/models/User.php defining the relationship – 001221
Just to make sure... you have roles defined in "assigned_roles" for your users? If there are no corresponding rows, then no results would be returned. – jedrzej.kurylo
@jedrzej.kurylo I don't have a model for this I'm using github.com/Zizaco/entrust/tree/1.0 but it doesn't have functionality to do this – 001221
ok, but is there any data in "assigned_roles" table? – jedrzej.kurylo
yes there is data that table @jedrzej.kurylo – 001221

1 Answers

1
votes

In order to fetch users that have admin role you need to use Eloquent's whereHas() method:

$admins = \User::with('profile')
  ->whereHas('assigned_roles', function($query) {
    $query->whereName('admin');
  })
  ->get();

I assume you have assigned_roles relation defined. If not, add many-to-many relation between your users and roles:

public function assigned_roles() {
  return $this->belongsToMany('\Your\Model\Namespace\Role', 'assigned_roles', 'user_id', 'role_id');
}