Laravel 4 Eloquent Table Join And Where Clause On Multiple Tables

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

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');
}