title: Laravel eloquent retrieve user role in multi select
body: <p>Hi I am using Laravel and I am using a couple of packages for user auth and roles which are <a href="https://github.com/Zizaco/confide" rel="nofollow">Zizaco Confide</a> and <a href="https://github.com/Zizaco/entrust" rel="nofollow">Zizaco Entrust</a>. I have run through the setup process and I have roles and permissions. This has created the following tables:</p>
  
  <p>users, roles, assigned_roles, permissions, permission_role</p>
  
  <p>The structure of the tables is below with two users:</p>
  
  <pre><code>users table
  id | username    | email 
  1  | adminuser   | admin@test.com
  2  | subscriber  | subscriber@test.com
  
  roles
  id | name 
  1  | admin
  2  | subscriber
  
  assigned_roles
  id | user_id | role_id
  1  | 1       | 1
  2  | 1       | 2
  3  | 2       | 2
  
  permissions
  id | name              | display_name
  1  | can_view_admin    | Can View Admin Display
  2  | can_view_articles | Can View Articles
  
  permission_role
  id | permission_id | role_id 
  1  | 1             | 1
  2  | 1             | 2
  3  | 2             | 2
  </code></pre>
  
  <p>I have an edit and update function which updates the user table. However I want to also update the users permission in this view also and in the Users Controller too. But I'm not sure how to fetch the users role as they can have many roles and inject this into a multi select.</p>
  
  <p>This is what I have so far:</p>
  
  <p>Users Controller :</p>
  
  <pre><code> public function edit($id)
      {
             $user = Auth::user();       
              if ($user-&gt;hasRole('Admin'))
                  {
                      $user = User::find($id);
                      return View::make('users.edit')-&gt;with('user', $user);
                  }
                  else {
                      return \Redirect::route('article');
                  }
      }
  
  
  
  public function update($id)
      {
  
          $rules     = array(
              'username' =&gt; 'required',
              'email'    =&gt; 'required'
          );
  
          $validator = Validator::make(Input::all(), $rules);
  
          // process the login
          if ($validator-&gt;fails()) {
              return Redirect::back('/admin/user/' . $id . '/edit')-&gt;withErrors($validator)-&gt;withInput();
  
          } else {
              // store
              $user               = User::find($id);
              $user-&gt;firstname    = Input::get('firstname');
              $user-&gt;lastname     = Input::get('lastname');
              $user-&gt;username     = Input::get('username');
              $user-&gt;email        = Input::get('email');
              $user-&gt;save();
  
              // redirect
              Session::flash('message', 'Successfully updated!');
              return Redirect::to('/admin');
          }
      }
  </code></pre>
  
  <p>My edit form is as below:</p>
  
  <pre><code>{{ HTML::ul($errors-&gt;all()) }}
  {{ Form::open(array('url' =&gt; URL::to('admin/user/'.$user-&gt;id.'/update'), 'method' =&gt; 'PATCH ')) }}
  {{ Form::model($user) }}
  
          {{ Form::text('firstname', null, array('class' =&gt; 'form-control')) }}
  
          {{ Form::text('lastname', null, array('class' =&gt; 'form-control')) }}
  
          {{ Form::text('username', null, array('class' =&gt; 'form-control')) }}
  
          {{ Form::text('email', null, array('class' =&gt; 'form-control')) }}
  
  
      {{ Form::submit('Update User', array('class' =&gt; 'btn btn-green')) }}
  {{ Form::close() }}
  </code></pre>
  
  <p>I know I can fetch all the roles as so:</p>
  
  <pre><code>$roles =  DB::table('roles')-&gt;orderBy('name', 'asc')-&gt;lists('name', 'id'); 
  </code></pre>
  
  <p>and then insert into a multi select as so:</p>
  
  <pre><code>{{ Form::select('role', $roles , Input::old('role'), array('class' =&gt; 'select')) }}
  </code></pre>
  
  <p>and I know you can get the user role as so:</p>
  
  <pre><code>$roles = User::find(1)-&gt;roles();
  </code></pre>
  
  <p>so I tried to change this line in my edit function </p>
  
  <pre><code>$user = User::find($id);
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>$user = User::find($id)-&gt;roles()-&gt;orderBy('name', 'asc')-&gt;lists('name', 'id');
  </code></pre>
  
  <p>But this throws this error:</p>
  
  <pre><code>SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field
   list is ambiguous (SQL: select `name`, `id` from `roles` inner join 
   `assigned_roles` on `roles`.`id` = `assigned_roles`.`role_id` where 
   `assigned_roles`.`user_id` = 1 order by `name` asc)
  </code></pre>
  
  <p>Does anyone know how I can fetch the users roles (as they can have more than one) using a function within entrust? Or possibly using eloquent as the data is stored in a few tables, for example in the edit screen I would want to grab the <code>user</code> <code>roles</code> by <code>user_id</code> this data is stored in the <code>assigned_roles</code> table. Then I would need to fetch the role name which is stored in the roles table and the <code>assigned_roles</code> table holds the <code>role_id</code>. I then need to put this into a multi select any ideas on how I can do that? I'm not great with eloquent so any ideas would be appreciated. </p>