如果关联具有限制子句，则关联关系属性上的 Rails 总和不正确 [英] Rails sum on AssociationRelation attribute is incorrect if association has limit clause

问题描述

我有一种方法可以计算模型中许多浮点属性的统计数据(主要是总和).

I have a method that computes stats (mainly sums) on a number of float attributes in a model.

模型

class GroupPlayer < ActiveRecord::Base
  belongs_to :group
  has_many :scored_rounds
  has_many :rounds, dependent: :destroy
end

class Round < ActiveRecord::Base
  belongs_to :group_player
end

class ScoredRound < Round
  # STI
end

提供最多 4 个浮点属性的统计数据的方法，这些属性是从其他方法调用的，具体取决于我是获取一个玩家还是一组玩家的统计数据.ScoredRound 上的初始过滤器传递给方法 (sr)

The method that provides stats on up to 4 float attributes that is called from a other methods, depending if I'm getting stats for one player or a group of players. An initial filter on ScoredRound is passed to the method (sr)

def method_stats(method,sr,grp)
  rounds = sr.where.not(method => nil)
  number_rounds = rounds.count
  won = rounds.sum(method).round(2)
  if method == :quality
    dues = grp.options[:dues] * number_rounds
  else
    dues = grp.options["#{method.to_s}_dues"] * number_rounds
  end
  balance = (won - dues).round(2)
  perc = dues > 0 ? (won / dues).round(3) : 0.0
  [self.full_name,number_rounds,won,dues,balance,perc]
end

如果玩家没有赢得那场比赛，我在 ScoredRounds 中求和的 4 个属性中的 3 个可能不会设置 (nil)，因此回合被过滤.

3 of the 4 attributes I am summing in ScoredRounds may not be set (nil) if the player did not win that game so the rounds are filtered.

一切正常，直到我决定对使用的轮数进行限制.例如，如果我只想要传递给 method_stats 的查询中最后 25 轮的状态，我会调用:

Everything worked fine until I decided to add a limit on how many rounds to use. For instance if I only wanted status for the last 25 rounds in the query passed to method_stats I'd call:

def money_stats(grp,method,limit=100)
  sr = self.scored_rounds.where.not(method => nil).order(:date).reverse_order.limit(limit)
  method_stats(method,sr,grp)
end

同样，我只是在查询中添加了 limit 和 order 子句.适用于所有记录.

Again, I just added the limit and order clause to the query. Worked fine for all records.

如果我不使用上述方法(或使用它们！)在控制台中模拟该过程，我将得到错误的总和

If I simulate the procedure in the console with out using the above methods (or using them!) I'll get an erroneous sum

gp = GroupPlayer.find(123)
  GroupPlayer Load (2.1ms)  SELECT  "group_players".* FROM "group_players" WHERE "group_players"."id" = $1 LIMIT $2  [["id", 123], ["LIMIT", 1]]
  => valid group player

sr = gp.scored_rounds.where.not(:quality => nil)
  ScoredRound Load (1.7ms)  SELECT "rounds".* FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL)  [["group_player_id", 123]]

  => #<ActiveRecord::AssociationRelation [#<ScoredRound id: 5706, player_id: 123, group_player_id: 123, event_id: 12, type: "ScoredRound", date: "2016-11-04", team: 3, tee: "White", quota: 32, front: 15, back: 15, total: 30, created_at: "2016-11-04 14:18:27", updated_at: "2016-11-04 19:12:47", quality: 0.0, skins: nil, par3: nil, other: nil>,...]

sr.count
   (1.5ms)  SELECT COUNT(*) FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL)  [["group_player_id", 123]]
  => 44

sr.sum(:quality)
   (1.0ms)  SELECT SUM("rounds"."quality") FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL)  [["group_player_id", 123]]
  => 354.166666666667

# Now if I add the order and limit clause

sr = gp.scored_rounds.where.not(:quality => nil).order(:date).reverse_order.limit(25)
  ScoredRound Load (1.6ms)  SELECT  "rounds".* FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL) ORDER BY "rounds"."date" DESC LIMIT $2  [["group_player_id", 123], ["LIMIT", 25]]
  => => #<ActiveRecord::AssociationRelation [...]

sr.count
   (1.1ms)  SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL) LIMIT $2) subquery_for_count  [["group_player_id", 123], ["LIMIT", 25]]
=> 25

sr.sum(:quality)
   (1.8ms)  SELECT  SUM("rounds"."quality") FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL) LIMIT $2  [["group_player_id", 123], ["LIMIT", 25]]
=> 354.166666666667

###  This is the error, it return the sum off all records, 
# not the limited???? if I use pluck and sum

sr.pluck(:quality)
=> [10.0, 11.3333333333333, 10.0, 34.0, 0.0, 7.33333333333333, 0.0, 0.0, 31.5, 0.0, 21.3333333333333, 0.0, 19.0, 0.0, 0.0, 7.5, 0.0, 20.0, 10.0, 28.0, 8.0, 9.5, 0.0, 3.0, 24.0]

sr.pluck(:quality).sum
=> 254.49999999999994

不知道是我在 AREL 中发现了错误还是我做错了什么.我仅使用 Round 而不是 STI ScoredRound 进行了尝试，结果相同.

Don't know if I found a bug in AREL or I'm doing something wrong. I tried it with just Round instead of the STI ScoredRound with the same results.

有什么想法吗?

推荐答案

如果你注意到，SUM 的结果，无论有没有 LIMIT，都是一样的:

If you notice, the SUM results for both, with and without LIMIT, are the same:

sr = gp.scored_rounds.where.not(:quality => nil)
sr.sum(:quality)
   (1.0ms)  SELECT SUM("rounds"."quality") FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL)  [["group_player_id", 123]]
  => 354.166666666667

sr = gp.scored_rounds.where.not(:quality => nil).order(:date).reverse_order.limit(25)
sr.sum(:quality)
   (1.8ms)  SELECT  SUM("rounds"."quality") FROM "rounds" WHERE "rounds"."type" IN ('ScoredRound') AND "rounds"."group_player_id" = $1 AND ("rounds"."quality" IS NOT NULL) LIMIT $2  [["group_player_id", 123], ["LIMIT", 25]]
=> 354.166666666667

那是因为 LIMIT 影响查询返回的行数而 SUM 只返回 1，所以该函数应用于所有 44 条记录，而不是给定的 25 条记录到 LIMIT.这不是 sr.pluck(:quality).sum 发生的情况，它仅适用于查询返回的 25 条记录.

That's because LIMIT affects the number of rows returned by the query and SUM returns just one, so the function is applied for all the 44 records, not the 25 given to LIMIT. That's not what happens with sr.pluck(:quality).sum which applies only to the 25 records returned by the query.

不知道是我在 AREL 中发现了错误还是我做错了什么

Don't know if I found a bug in AREL or I'm doing something wrong

遗憾的是，99.9% 的情况不是错误而是我们的错:(

Sadly, 99.9% of times is not a bug but our fault :(

这篇关于如果关联具有限制子句，则关联关系属性上的 Rails 总和不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！