如何将SQL查询转换为Rails Active Record查询? [英] How to Convert SQL Query to Rails Active Record Query?
问题描述
我的上一篇文章是关于在LEFT OUTER JOIN上编写带有条件的SQL查询的最佳方法:
是否有条件(在何处,按何者排序)的外部联接?
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)?
现在,我需要将该段好的SQL转换成一个不错的Active Record查询(Rails 3). ;-)
Now, I need to convert that good piece of SQL into a sweet Active Record Query (Rails 3). ;-)
我有2种型号:
培训 has_many:training_histories
Training has_many :training_histories
培训历史属于_培训:p
如何编写范围以获取下面的SQL检索的结果?
How can I write a scope to get the results the SQL below retrieve ?
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
我想检索TRAININGS的所有记录,并添加TRAINING_HISTORIES的最后一个有效(也称为完成)的关联记录.
I want to retrieve all the records of TRAININGS and add the last valid (aka finished) associated record of TRAINING_HISTORIES.
有什么建议吗?
谢谢
推荐答案
对于Rails 3,请尝试以下操作:
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")
基本上,您只是将预先编写的查询转换为Rails 3 finder方法.
Basically, you're just converting your pre-written query into Rails 3 finder methods.
这篇关于如何将SQL查询转换为Rails Active Record查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!