title: How to Convert SQL Query to Rails Active Record Query?
body: <p>My last post was on the best way to write a SQL query with conditions on a LEFT OUTER JOIN:<br>
  <a href="https://stackoverflow.com/questions/7015839/left-outer-join-with-conditions-where-order-by">LEFT OUTER JOIN with conditions (where, order by)?</a></p>
  
  <p>Now, I need to convert that good piece of SQL into a sweet Active Record Query (Rails 3). ;-)</p>
  
  <p>I have 2 Models: </p>
  
  <p><strong>Training</strong> has_many :training_histories</p>
  
  <p><strong>TrainingHistory</strong> belongs_to :training</p>
  
  <p>How can I write a scope to get the results the SQL below retrieve ?</p>
  
  <pre><code>SELECT tc.id, tc.name, tc.order, 
  th.id as history_id, th.finished_at, th.score
  FROM trainings tc
  LEFT OUTER JOIN training_histories th ON th.training_id = tc.id 
      and th.id =
      (SELECT th1.id from training_histories th1 where th1.training_id = tc.id
       and th1.finished_at is not null
       order by th1.finished_at desc limit 1)
  WHERE tc.id &gt; 4
  AND tc.id &lt; 8
  GROUP BY tc.id
  ORDER BY tc.order_by ASC, tc.id ASC
  </code></pre>
  
  <p>I want to retrieve all the records of TRAININGS and add the last valid (aka finished) associated record of TRAINING_HISTORIES.</p>
  
  <p>Any suggestions ?</p>
  
  <p>Thank you</p>