title: Eager load a multiple indexes relationship with Laravel 5
body: <p>I'm currently working on a project where I have to use a 10 years old database which is badly constructed.
  Anyway I have two tables: one for the products and one for their colors. Every product is identified by a reference and every color is identified by its own reference + its product's one.
  For instance I have a product which has the reference 123. This product has 3 colors: 123-01, 123-02 and 123-03. These two values are split in two columns: nocol (product reference) and nopat (color reference). 
  I can't have a unique id for each color as the database is dropped every night so the ids are always changing (I know this is pretty weird).</p>
  
  <p>For now I have three models: Product, Color and CartItem. My Color model has a one-to-one relationship with the Product model. My CartItem has a one-to-one relationship with my Color model which is defined like this:</p>
  
  <pre><code>public function color() {
    return $this-&gt;hasOne('App\Color', 'nocol', 'color_ref')-&gt;where('nopat', $this-&gt;product_ref);
  }
  </code></pre>
  
  <p>This is working fine because I can retrieve the Color from the CartItem with a simple <code>$cartItem-&gt;color</code>. The problem is that it doesn't work with eager loading. When I try to retrieve my CartItems with something like this:</p>
  
  <pre><code>CartItem::with('product', 'color')-&gt;get();
  </code></pre>
  
  <p>I end up with a null relationship when I dump the result.</p>
  
  <p>Is there any other way to write this kind of relationship when the index is two separate columns?</p>
  
  <hr>
  
  <p><strong>EDIT 07-10</strong></p>
  
  <p>As requested, here is a very simple view of the database. As I said I had to use an old database which I can't modify. The easiest way to solve my problem would be to add a unique ID to my Color table but I'm not able to do this.</p>
  
  <p>My Product model has a unique id <code>pat_nopat</code>. One Product can have many colors. My Color model has two columns <code>col_nopat</code> and <code>col_nocol</code>. The <code>col_nopat</code> column references the Product ID. Together, the two columns are a unique ID for the Color model.</p>
  
  <p>My OrderItem model references a Color by both <code>col_nopat</code> and <code>col_nocol</code> which are called <code>product_ref</code> and <code>color_ref</code>.</p>
  
  <p><a href="https://i.stack.imgur.com/SRVgl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SRVgl.png" alt="enter image description here"></a></p>
  
  <p>When I display the order history for a specific customer, I also display some information about the Color through the OrderItem model. The problem is that it executes too much queries because I can't find a way to eager load the relationship between the OrderItem and the Color.</p>
  
  <hr>
  
  <p><strong>EDIT 07-11</strong></p>
  
  <p>I tried to change my relationship using a join clause instead of a where clause. The eager loading works but the results are wrong.</p>
  
  <p>If I have two CartItems with the same color reference but a different product reference, the results I get are wrong.
  Let's say I have two CartItems like this:</p>
  
  <pre><code>product_ref | color_ref
  ----------- | ---------
  1234        | 01
  5678        | 01
  </code></pre>
  
  <p>I get the correct results with the where relationship except I can't eager load.</p>
  
  <p>However, the join relationship returns 1234-01 for both rows because it takes the first result for each color reference.
  If I take a look at the queries in the debugbar I get the following:</p>
  
  <pre><code>select * from `colors` inner join `cart_items` on `colors`.`col_nopat` = `cart_items`.`product_ref` where `colors`.`col_nocol` in ('01')
  select * from `products` where `products`.`pat_nopat` in ('1234', '5678')
  </code></pre>