Rails Association Question

I have three models: User, RaceWeek, Race.

Current associations:

class User < ActiveRecord::Base
  has_many :race_weeks
end

class RaceWeek < ActiveRecord::Base
  belongs_to :user
  has_many :races
end

class Race < ActiveRecord::Base
  belongs_to :race_week
end

So the user_id is a foreign key in RaceWeek and race_week_id开发者_如何学C is a foreign key in Race.

fastest_time is an attribute of the Race model.

QUESTION: What's the optimal way to retrieve a list of users who have the top X fastest race times?

You can do it like this:

users = User.all(:limit => X, :joins => {:race_weeks => :races}, :order => "reces.fastest_time DESC").uniq

If you have correctly specified has_many :through association, then you could even do it like this:

users = User.all(:limit => X, :joins => :races, :order => "reces.fastest_time DESC").uniq

In this solution, you get what you want with one query, but two joins. And this uniq method is not very good unless you would use small X.

Something like:

 races = Race.all(:order => "fastest_time desc", :limit => X, :include => {:race_week => :user})
 users = races.map{|race| race.race_week.user}.uniq

Note: didn't test this.

Given your current model the following should work.

race_weeks = RaceWeek.find_by_sql(["SELECT user_id FROM race_weeks JOIN races ON races.race_week_id = race_weeks.id ORDER BY races.fastest_time desc LIMIT ?", X)
users = User.find(race_weeks.collect(&:user_id).uniq)

I know that it requires two look ups but the second lookup should be very fast since you are only looking up X records by their primary key.