代码之家 › 专栏 › 技术社区 › fantactuka

SQL:从查询中获取选定的行索引

activerecord ruby-on-rails sql

fantactuka · 技术社区 · 15 年前

我有一个应用程序,存储每个锦标赛的球员评分。所以我有很多联系:

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

rating 字段(float),用于存储每个锦标赛中每个玩家的评分值(类似于分数)。

我想要的是得到最后10个等级的球员( rank 评级 字段并使用ruby代码获取玩家的参与索引:

class Participation < ActiveRecord::Base

  belongs_to :player
  belongs_to :tournament

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

方法 等级 的参与获取其父锦标赛,加载所有锦标赛NTR的参与者(按 rating desc

然后是这样的:

player.participations.last.rank

有一件事我不喜欢-它需要加载所有参赛者的比赛,并在案件中,我需要球员排名为过去10个比赛,它加载超过5000个项目(其数量将增长时,新的球员加入)。

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;")

此查询从给定的锦标赛中选择前10名。我一直在尝试修改它,为给定的用户和他的排名选择最后10个参与者。

谢谢你的帮助(我认为解决方案将是一个SQL请求,因为它对于ActiveRecord来说相当复杂。

另外,我用的是rails3.0.0.beta4

这里是最后一个sql请求,它获取玩家的最后10个等级(此外,它还加载参与的比赛)

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;

2 回复 | 直到 15 年前

Max Williams 15 年前

首先,应该:

Participation
  belongs_to :tournament
  belongs_to :players

是这个吗?

Participation
  belongs_to :tournament
  belongs_to :player

我努力想弄清楚这是怎么回事:

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

你没有对你的模式做足够的解释,使它更容易进行反向工程。它是在做以下的。。。?

“获取给定巡演的所有参与者,并返回该列表中当前参与者的位置”?你是这样计算军衔的吗?如果是这样的话,我同意这似乎是一个非常复杂的方法。

你是否为玩家得到的10个参与对象做了上述操作,然后取平均值?什么是评级?这和等级有关系吗?基本上,你能解释你的模式多一点,然后重申你想做什么吗?

我想你只是需要一个更有效的方法来找到这个职位。有一种方法我可以从脑子里想出来——得到你想要的唱片,然后数一数上面有多少张。再加上1,你就得到了这个职位。如

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

您应该在日志文件中看到(例如在控制台中进行实验时),这只是一个计数查询。如果在rating字段上有索引(如果没有索引,则应该有索引),那么这将是一个非常快的查询。

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

fantactuka 15 年前

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;