title: Laravel Eloquent: Multiple Relationships between two Models, One-to-One AND One-to-Many
body: <p>I am quite new to both, Laravel and Eloquent and while the basics are getting along, I am researching and trying for some time to get this to work:</p>
  
  <p>I have got a User model and a Character model (World of Warcraft players might relate to the issue), a User has many Characters and a Character belongs to exactly one User.</p>
  
  <p>I want to store additional information about this relationship (wether this Character is the User's MAIN character or one of many TWINK characters), so I tried implementing a pivot table with an additional field (<em>role</em>), but I can't get it to work.</p>
  
  <p>The <strong>migrations</strong> are as follows:</p>
  
  <pre><code>Schema::create('users', function ($table) {
      $table-&gt;increments('id');
      $table-&gt;string('username')-&gt;unique();
      $table-&gt;timestamps();
  });
  
  Schema::create('characters', function($table)
  {
      $table-&gt;increments('id');
      $table-&gt;timestamps();
      $table-&gt;string('name');
  });
  
  Schema::create('characters_users', function($table)
  {
      $table-&gt;increments('id');
      $table-&gt;integer('user_id')-&gt;unsigned();
      $table-&gt;integer('character_id')-&gt;unsigned();
      $table-&gt;foreign('user_id')-&gt;references('id')-&gt;on('users');
      $table-&gt;foreign('character_id')-&gt;references('id')-&gt;on('characters');
      $table-&gt;string('type')-&gt;default('MAIN'); // can be 'MAIN' or 'TWINK'
  });
  </code></pre>
  
  <p>In the <strong>Characters Model</strong>, I have defined the following:</p>
  
  <pre><code>public function user() 
  {
      return $this-&gt;hasOne('User', 'characters_users.user_id');
  }
  </code></pre>
  
  <p>In the <strong>Users Model</strong> I have defined the following:</p>
  
  <pre><code>public function main() 
  {
      return $this-&gt;hasOne('Character', 'characters_users.character_id', 'id')-&gt;where('characters_users.role', '=', 'MAIN');
  }
  
  public function twinks()
  {
      return $this-&gt;hasMany('Character', 'characters_users.character_id', 'id')-&gt;where('characters_users.role', '=', 'TWINK');
  }
  </code></pre>
  
  <p>Now I am trying to setup an example relationship, I get both a user and a character and want to say: This is the main character of this user:</p>
  
  <pre><code>$user = User::find(1)-&gt;first();
  
  $char = Character::whereName('Bruise')-&gt;first();
  
  // assign this char as main to the user
  $user-&gt;main()-&gt;save($char);
  </code></pre>
  
  <p>The error message is:</p>
  
  <pre><code>SQLSTATE[42S22]: Column not found: 1054 Unknown column 'character_id' in 'field list' (SQL: update `characters` set `updated_at` = 2014-09-05 12:30:58, `character_id` = 1 where `id` = 47)
  </code></pre>
  
  <p>because - of course, it's a one-to-one relationship - Eloquent searches for the corresponding fields to update not in the pivot table, but in the tables of the character resp. user.</p>
  
  <p><strong>What I want to achieve</strong></p>
  
  <ul>
  <li>Setting a character as MAIN to a user</li>
  <li>Setting characters as TWINK to a user</li>
  <li>Querying "What's the users MAIN character, what are his/her TWINK characters?"</li>
  </ul>
  
  <p>Is there a better, smarter way to do this instead of using a pivot table, am I completely off-track in this issue?</p>