如何将SQL查询转换为Rails活动记录查询？

Question

我的最后一个职位是在写一个SQL查询与一个LEFT OUTER条件最好的方式加盟：
LEFT OUTER JOIN with conditions (where, order by)?

现在，我需要的是好片的SQL转换成甜蜜的Active Record查询（Rails 3的）。 ;-)

我有2种型号：

培训的has_many：training_histories

TrainingHistory belongs_to的：训练

我如何写一个范围，得到的结果下面的SQL检索？

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 > 4 
AND tc.id < 8 
GROUP BY tc.id 
ORDER BY tc.order_by ASC, tc.id ASC 

我想检索培训的所有记录，并添加TRAINING_HISTORIES的有关最后一个有效（又名完）记录。

有什么建议吗？

谢谢

Answer 1

钢轨3，试试这个：

Training.select("trainings.id, trainings.name, trainings.order, 
       trainings.id AS history_id, training_histories.finished_at, 
       training_histories.score"). 
     joins("LEFT OUTER JOIN training_histories 
       ON training_histories.training_id = trainings.id 
       AND training_histories.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("trainings.id > 4 AND trainings.id < 8"). 
     group("trainings.id"). 
     order("trainings.order_by ASC, trainings.id ASC") 

基本上，你只是转换您预先写好的查询到Rails 3 finder方法。