title: Laravel 4 Eloquent table join and where clause on multiple tables
body: <p>Hi I'm using Laravel and Eloquent in order to store users, there profile and there assigned roles. My table structure is as follows;</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> return \User::with('profile')-&gt;join('assigned_roles', function ($join) {
          $join-&gt;where('assigned_roles.user_id', '=', 'users.id');
      })-&gt;get();
  </code></pre>
  
  <p>Any ideas what I'm doing wrong?</p>
  
  <p>Also I am using Zizaco Entrust and Confide <a href="https://github.com/Zizaco/entrust/tree/1.0" rel="nofollow">https://github.com/Zizaco/entrust/tree/1.0</a> </p>