Rails Association Question...

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 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?

Answer 1

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.

Answer 2

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.

Answer 3

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.