CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3
Texas
Texas

Reputation: 913

Issues with DISTINCT when used in conjunction with ORDER

I am trying to construct a site which ranks performances for a selection of athletes in a particular event - I have previously posted a question which received a few good responses which me to identify the key problem with my code currently.

I have 2 models - Athlete and Result (Athlete HAS MANY Results)

Each athlete can have a number of recorded times for a particular event, i want to identify the quickest time for each athlete and rank these quickest times across all athletes.

I use the following code:

 <% @filtered_names = Result.where(:event_name => params[:justevent]).joins(:athlete).order('performance_time_hours ASC').order('performance_time_mins ASC').order('performance_time_secs ASC').order('performance_time_msecs ASC') %>

This successfully ranks ALL the results across ALL athletes for the event (i.e. one athlete can appear a number of times in different places depending on the times they have recorded).

I now wish to just pull out the best result for each athlete and include them in the rankings. I can select the time corresponding to the best result using:

 <% @currentathleteperformance = Result.where(:event_name => params[:justevent]).where(:athlete_id => filtered_name.athlete_id).order('performance_time_hours ASC').order('performance_time_mins ASC').order('performance_time_secs ASC').order('performance_time_msecs ASC').first() %>

However, my problem comes when I try to identify the distinct athlete names listed in @filtered_names. I tried using <% @filtered_names = @filtered_names.select('distinct athlete_id') %> but this doesn't behave how I expected it to and on occasions it gets the rankings in the wrong order.

I have discovered that as it stands my code essentially looks for a difference between the distinct athlete results, starting with the hours time and progressing through to mins, secs and msec. As soon as it has found a difference between a result for each of the distinct athletes it orders them accordingly.

For example, if I have 2 athletes:

Time for Athlete 1 = 0:0:10:5
Time for Athlete 2 = 0:0:10:3

This will yield the order, Athlete 2, Athlete1

However, if i have:

Time for Athlete 1 = 0:0:10:5
Time for Athlete 2 = 0:0:10:3
Time for Athlete 2 = 0:1:11:5

Then the order is given as Athlete 1, Athlete 2 as the first difference is in the mins digit and Athlete 2 is slower...

Can anyone suggest a way to get around this problem and essentially go down the entries in @filtered_names pulling out each name the first time it appears (i.e. keeping the names in the order they first appear in @filtered_names

Thanks for your time

Upvotes: 1

Views: 103

Answers (2)

Harish Shetty
Harish Shetty

Reputation: 64363

You should use DB to perform the max calculation, not the ruby code. Add a new column to the results table called total_time_in_msecs and set the value for it every time you change the Results table.

class Result < ActiveRecord::Base

  before_save :init_data

  def init_data
    self.total_time_in_msecs = performance_time_hours * MSEC_IN_HOUR +       
                                performance_time_mins * MSEC_IN_MIN +  
                                performance_time_secs * MSEC_IN_SEC + 
                                performance_time_msecs
  end

  MSEC_IN_SEC  = 1000
  MSEC_IN_MIN  = 60 * MSEC_IN_SEC
  MSEC_IN_HOUR = 60 * MSEC_IN_MIN
end

Now you can write your query as follows:

athletes = Athlete.joins(:results).
  select("athletes.id,athletes.name,max(results.total_time_in_msecs) best_time").
  where("results.event_name = ?", params[:justevent])
  group("athletes.id, athletes.name").
  orde("best_time DESC")


athletes.first.best_time # prints a number

Write a simple helper to break down the the number time parts:

def human_time time_in_msecs

  "%d:%02d:%02d:%03d" %   
    [Result::MSEC_IN_HOUR, Result::MSEC_IN_MIN, 
        Result::MSEC_IN_SEC, 1 ].map do |interval|
      r = time_in_msecs/interval
      time_in_msecs = time_in_msecs % interval
      r
    end

end

Use the helper in your views to display the broken down time.

Upvotes: 0

Brandan
Brandan

Reputation: 14983

If you're on Ruby 1.9.2+, you can use Array#uniq and pass a block specifying how to determine uniqueness. For example:

@unique_results = @filtered_names.uniq { |result| result.athlete_id }

That should return only one result per athlete, and that one result should be the first in the array, which in turn will be the quickest time since you've already ordered the results.

One caveat: @filtered_names might still be an ActiveRecord::Relation, which has its own #uniq method. You may first need to call #all to return an Array of the results:

@unique_results = @filtered_names.all.uniq { ... }

Upvotes: 1

Related Questions