How To Convert Sql Query To Rails Active Record Query?

My last post was on the best way to write a SQL query with conditions on a LEFT OUTER JOIN: LEFT OUTER JOIN with conditions (where, order by)? Now, I need to convert that good piec

Solution 1:

For rails 3, try this:

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")

Basically, you're just converting your pre-written query into Rails 3 finder methods.