Rails query on multiple primary keys with conditions on association

Is there a way in Active Record to construct a single query that will do a conditional join for multiple primary keys?

Say I have the following models:

Class Athlete < ActiveRecord::Base
  has_many :workouts
end

Class Workout < ActiveRecord::Base
  belongs_to :athlete
  named_scope :run, :conditions => {:type => "run"}
  named_scope :best, :order => "time", :limit => 1
end

With that, I could generate a query to get the best run time for an athlete:

  Athlete.find(1).workouts.run.best

How can I get the best run time for each athlete in a group, using a single query?

The following does not work, because it applies the named scopes just once to the whole array, returning the single best time for all athletes:

开发者_运维问答

Athlete.find([1,2,3]).workouts.run.best

The following works. However, it is not scalable for larger numbers of Athletes, since it generates a separate query for each Athlete:

[1,2,3].collect {|id| Athlete.find(id).workouts.run.best}

Is there a way to generate a single query using the Active Record query interface and associations?

If not, can anyone suggest a SQL query pattern that I can use for find_by_SQL? I must confess I am not very strong at SQL, but if someone will point me in the right direction I can probably figure it out.

---

To get the Workout objects with the best time:

athlete_ids = [1,2,3]
# Sanitize the SQL as we need to substitute the bind variable
# this query will give duplicates
join_sql    = Workout.send(:santize_sql, [ 
    "JOIN (
      SELECT a.athlete_id, max(a.time) time 
      FROM   workouts a
      WHERE  a.athlete_id IN (?)
      GROUP BY a.athlete_id
    ) b ON b.athlete_id = workouts.athlete_id AND b.time = workouts.time", 
    athlete_ids])


Workout.all(:joins => join_sql, :conditions => {:athlete_id => })

If you require just the best workout time per user then:

Athlete.max("workouts.time", :include => :workouts, :group => "athletes.id", 
 :conditions => {:athlete_id => [1,2,3]}))

This will return a OrderedHash

{1 => 300, 2 => 60, 3 => 120}

Edit 1

The solution below avoids returning multiple workouts with same best time. This solution is very efficient if athlete_id and time columns are indexed.

Workout.all(:joins => "LEFT OUTER JOIN workouts a 
  ON workouts.athlete_id  = a.athlete_id AND 
     (workouts.time < b.time OR workouts.id < b.id)",
  :conditions => ["workouts.athlete_id = ? AND b.id IS NULL", athlete_ids]
)

Read this article to understand how this query works. Last check (workouts.id < b.id) in the JOIN ensures only one row is returned when there are more than one matches for the best time. When there are more than one match to the best time for an athlete, the workout with the highest id is returned(i.e. the last workout).

---

Certainly following will not work

Athlete.find([1,2,3]).workouts.run.best

Because Athlete.find([1,2,3]) returns an array and you can't call Array.workouts

You can try something like this:

 Workout.find(:first, :joins => [:athlete], :conditions => "athletes.id IN (1,2,3)", :order => 'workouts.time DESC')

You can edit the conditions according to your need.