Ruby on Rails - Calculate Rank

Question

On Rails 4. I am building a contest application. I have three tables relevant to this use case:

Submissions - Attributes include:

• :contest_year - The year the submission was created
• :category_id - The contest category the submission is assigned to
• :division_id - The contest division the submission is assigned to (out of five)

Scores - Attributes include:

• :user_id - The ID of the judge who gives the score
• :submission_id - The submission ID the score is tied to
• :total_score - The integer number the judge provides as a score of the entry; usually out of ten

Judges - Attributes include:

• :user_id - The user ID of the judge
• :category_id - The category the judge is assigned to score
• :division_id - The division the judge is assigned to score

One submission can have many scores (as there are multiple judges assigned to its category/division).

The first thing the app must do is find the submission's combined, final score out of all its child scores the various judges give it. I do this using a calculation in submission.rb:

  def calculate_final_score
    self.scores.average(:total_score)
  end

So basically, it finds all the child scores of the submission and takes the average of those to find the final score. This final score is NOT an updated attribute of the submission table, it is a method calculation result.

Now, here is where my question is. I need to find a way to calculate the submission's ranking compared to other submissions with the SAME :contest_year, :category_id, and :division_id by comparing that above final score calculation. The ranking system MUST give the same rank when submissions have the same final score (a tie).

tl;dr, I need ranking behavior like this (sample submissions table):

----------------------------------------------------------------------
| ID | Contest Year | Division ID | Category ID | Final Score | Rank |
|--------------------------------------------------------------------|
|  1 |         2013 |           1 |           1 |          10 |    1 |
|--------------------------------------------------------------------|
|  2 |         2013 |           1 |           1 |          10 |    1 |
|--------------------------------------------------------------------|
|  3 |         2013 |           1 |           1 |           8 |    2 |
|--------------------------------------------------------------------|
|  4 |         2013 |           1 |           1 |           6 |    3 |
|--------------------------------------------------------------------|
|  4 |         2013 |           1 |           1 |           5 |    4 |
|--------------------------------------------------------------------|
|  5 |         2013 |           2 |           1 |           8 |    1 |
|--------------------------------------------------------------------|
|  6 |         2014 |           1 |           2 |           9 |    1 |
|--------------------------------------------------------------------|
|  7 |         2014 |           1 |           2 |           7 |    2 |
----------------------------------------------------------------------

This ranking information will be placed in my Active Admin submissions' index table page (and also as part of the CSV table output).

Answer 1

I am not much familiarly with Rails (or with Ruby). But in our Django application we had a similar situation where we needed to calculate ranks on some computed data. As we were using PostgreSQL as the DB backend, we used Postges's window functions (http://www.postgresql.org/docs/9.1/static/functions-window.html, http://www.postgresql.org/docs/9.1/static/functions-window.html, http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) for rank calculation. Following is a sample PostgresSQL query which can produce the result required by your question.

sql_query = %Q{SELECT 
   *, dense_rank() OVER (
       PARTITION BY contest_year, category_id, division_id
       ORDER BY final_score DESC
   )
FROM (
    SELECT 
        Submissions.id, 
        Submission.contest_year AS contest_year, 
        Submission.category_id AS category_id, 
        Submission.division_id AS division_id, 
        AVG(Scores.total_score) AS final_score
    FROM Submissions 
        INNER JOIN Scores ON (Submissions.id = Scores.submission_id)
    GROUP BY 
        Submissions.id, 
        Submission.contest_year, 
        Submission.category_id, 
        Submission.division_id
) AS FinalScores}

submissions_by_rank = Submission.find_by_sql(sql_query)

Note: You will need to add ORDER BY clause in the query if you want to order the result in a particular fashion.

Answer 2

In Ruby, assuming you have that #calculate_final_score method set up already:

class Submission < ActiveRecord::Base
  def self.rank_all_submissions_by_group
    keys = [ :contest_year, :category_id, :division_id ]
    submission_groups = Submission.all.group_by do |sub|
      values = keys.map { |k| sub.send(key) }
      Hash[key.zip(values)]
    end

    return ranked_submissions_by_group = submission_groups.map do |group, submissions|
      group[:ranked_submissions] = submissions.map do |s|
        [s, s.calculate_final_score]
      end.sort_by(&:last)
    end
  end
end

The data that'll be returned will look like:

[
  {
    :contest_year => 2013,
    :division_id => 1,
    :category_id => 1,
    :ranked_submissions => [
      [ <Submission>, 10 ],
      [ <Submission>, 10 ],
      [ <Submission>, 8  ],
      [ <Submission>, 6  ],
      [ <Submission>, 5  ],
    ],
  },
  {
    :contest_year => 2013,
    :division_id => 2,
    :category_id => 1,
    :ranked_submissions => [
      [ <Submission>, 8 ],
    ],
  },
  {
    :contest_year => 2014,
    :division_id => 1,
    :category_id => 2,
    :ranked_submissions => [
      [ <Submission>, 9 ],
      [ <Submission>, 7 ],
    ],
  },
]

It won't be very performant though, since it just goes through all submissions for all time, makes a lot of copies (I think?), and I'm not using the right sorting algorithm.

If you have any issues/concerns, let me know, and I can update my answer.

Additional Help

If you're looking for a way to run a raw query in Ruby, consider this trivial example a suggestion:

full_submissions_table_as_hash = ActiveRecord::Base.connection.select_all(<<-mysql)
  SELECT *
  FROM #{Submissions.table_name}
mysql

Another trivial example showing how the data can be rendered from a controller:

# assuming routes are properly set up to actions in HomeController
class HomeController < ActionController::Base
  def ranked_submissions_text
    render :text => Submission.rank_all_submissions_by_group
  end
  def ranked_submissions_json
    render :json => Submission.rank_all_submissions_by_group
  end
end