title: Rails associations with lookup table that has a two column primary key
body: <p>CD table looks something like this:</p>
  
  <pre><code>cd.dogtag (primary key)
  ...
  </code></pre>
  
  <p><code>cd_tracks</code> has <code>id</code> and <code>tracks</code> (track name), example:</p>
  
  <pre><code>1   Turd On The Run
  </code></pre>
  
  <p><code>cd_tracks2title</code> is a lookup table to join many to many from the CD table and the <code>cd_tracks</code> table. But it also contains info I want to get:</p>
  
  <pre><code>cd_tracks2title.dogTag
  cd_tracks2title.trackId
  cd_tracks2title.duration
  cd_tracks2title.position
  </code></pre>
  
  <p>The primary key for <code>cd_tracks2title</code> is in fact 2 columns: </p>
  
  <pre><code>PRIMARY KEY (`dogTag`,`trackId`)
  </code></pre>
  
  <p>And the data inside <code>cd_tracks2title</code> might look like this:</p>
  
  <pre><code>1   4   NULL    NULL    3:24    11
  </code></pre>
  
  <p>The problem is I'm trying to establish a Rails association from CD table to cd_tracks2title using those 2 primary columns, but there doesn't seem to be a way. </p>
  
  <p>If I do this:</p>
  
  <pre><code># cd_track.rb
  has_and_belongs_to_many :cd, :join_table =&gt; 'cd_tracks2title', :foreign_key =&gt; 'trackId', :association_foreign_key =&gt; 'dogTag'
  
  #cd.rb
  has_and_belongs_to_many :cd_track, :join_table =&gt; 'cd_tracks2title', :foreign_key =&gt; 'dogTag', :association_foreign_key =&gt; 'trackId'
  </code></pre>
  
  <p>That's fine and I can get the track names with something like <code>Cd.first.cd_track</code>, but I don't have a way to get the <code>cd_tracks2title.duration</code> and <code>cd_tracks2title.position</code> info since it's a simple lookup table. </p>
  
  <p>Ideally, I'd keep this association so I can still get the track name, but would need another association from the CD table to the <code>cd_tracks2title</code> table, via the <code>cd_tracks</code> table using both the <code>cd.dogTag</code> and <code>cd_tracks.id</code> values to get the correct information. </p>
  
  <p>Of course, I used to do all this by explicitly writing out the Mysql JOINs in the query, but I'd like to create the Rails associtaion.  </p>