SQL: Get a selected row index from a query

Question

Hi everyone!

I have an applications that stores players ratings for each tournament. So I have many-to-many association:

Tournament
  has_many :participations, :order => 'rating desc'
  has_many :players, :through => :participations

Participation
  belongs_to :tournament
  belongs_to :player

Player
  has_many :participations
  has_many :tournaments, :through => :participations

The Participation model has a rating field (float) that stores rating value (it's like score points) for each player at each tournament.

The thing I want - get last 10 ranks of the player (rank is a position of the player at particular tournament based on his rating: the more rating - the higher rank). For now to get a player's rank on a tournament I'm loading all participations for this tournament, sort them by rating field and get the player's participation index with ruby code:

class Participation < ActiveRecord::Base

  belongs_to :player
  belongs_to :tournament

  def rank
    tournament.participations.index(self)
  end
end

Method rank of the participation gets its parent tournament, loads all tournamentr's participations (ordered by rating desc) and get own index inside this collection

and then something like:

player.participations.last.rank

The one thing I don't like - it need to load all participations for the tournament, and in case I need player ranks for last 10 tournaments it loads over 5.000 items (and its amount will grow when new players added).

I believe that there should be way to use SQL for it. Actually I tried to use SQL variables:

find_by_sql("select @row:=@row+1 `rank`, p.* from participations p, (SELECT @row:=0) r where(p.tournament_id = #{tournament_id}) order by rating desc limit 10;")

This query selects top-10 ratings from the given tournament. I've been trying to modify it to select last 10 participations for a given user and his rank.

Will appreciate any kind of help. (I think solution will be a SQL request, since it's pretty complex for ActiveRecord).

P.S. I'm using rails3.0.0.beta4

UPD:

Here is final sql request that gets last 10 ranks of the player (in addition it loads the participated tournaments as well)

SELECT *, (
 SELECT COUNT(*) FROM participations AS p2 
  WHERE p2.tour_id = p1.tour_id AND p2.rating > p1.rating
 ) AS rank
 FROM participations AS p1 LEFT JOIN tours ON tours.id = p1.tour_id  WHERE p1.player_id = 68 ORDER BY tours.date desc LIMIT 10;

Answer 1

First of all, should this:

Participation
  belongs_to :tournament
  belongs_to :players

be this?

Participation
  belongs_to :tournament
  belongs_to :player

Ie, singular player after the belongs_to?

I'm struggling to get my head around what this is doing:

class Participation
  def rank_at_tour(tour)
    tour.participations.index(self)
  end
end

You don't really explain enough about your schema to make it easy to reverse engineer. Is it doing the following...?

"Get all the participations for the given tour and return the position of this current participation in that list"? Is that how you calculate rank? If so i agree it seems like a very convoluted way of doing it.

Do you do the above for the ten participation objects you get back for the player and then take the average? What is rating? Does that have anything to do with rank? Basically, can you explain your schema a bit more and then restate what you want to do?

EDIT

I think you just need a more efficient way of finding the position. There's one way i could think of off the top of my head - get the record you want and then count how many are above it. Add 1 to that and you get the position. eg

class Participation
  def rank_at_tour(tour)
    tour.participations.count("rating > ?", self.rating) + 1
  end
end

You should see in your log file (eg while experimenting in the console) that this just makes a count query. If you have an index on the rating field (which you should have if you don't) then this will be a very fast query to execute.

Also - if tour and tournament are the same thing (as i said you seem to use them interchangeably) then you don't need to pass tour to participation since it belongs to a tour anyway. Just change the method to rank:

class Participation
  def rank
    self.tour.participations.count("rating > ?", self.rating) + 1
  end
end

Answer 2

SELECT *, (
 SELECT COUNT(*) FROM participations AS p2 
  WHERE p2.tour_id = p1.tour_id AND p2.rating > p1.rating
 ) AS rank
 FROM participations AS p1 LEFT JOIN tours ON tours.id = p1.tour_id  WHERE p1.player_id = 68 ORDER BY tours.date desc LIMIT 10;