相当于活动记录查询的SQL

Question

我有一个查询，我已经做得很好

Team.all.map do |team|
message = CoachFee.find_one(team_id: team.id).team_price_explanation[:prices]
end

我的'team'类只是

{
:id => 1,
:team_id => 6,
:created_at => nil,
:player_id => nil,
:updated_at => nil,
:user_id => 1417
}

team类与CoachFee类是一对多的关系。

唯一的问题是，我在我的应用程序中有超过300,000个CoachFee的数据库值，因此运行它可能需要永远。(超过4分钟)。

是否有任何sql大师在那里知道如何快速地将CoachFee.find_one(team_id: team.id)从活动记录替换为SQL?

提前感谢任何人!

Answer 1

试一试:

CoachFee
.where(id: CoachFee.select(CoachFee.arel_table[:id].minimum).group(:team_id))
.map {|cf| cf.team_price_explanation[:prices]} 

这将导致以下SQL

SELECT 
coach_fees.*
FROM 
coach_fees 
WHERE 
coach_fees.id IN ( 
SELECT 
MIN(coach_fees.id) 
FROM 
coach_fees
GROUP BY 
coach_fees.team_id
)