如何在单个查询中而不是在N+1中获得此查询结果

Question

一个pool_tournament有多个pool_tournament_matches，每个匹配属于多个users。用户has_many pool_tournaments和has_many pool_tournament_matches。

pool_tournament.rb

has_many :pool_tournament_matches

pool_tournament_match.rb

belongs_to :pool_tournament
has_many :pool_tournament_match_users, class_name: 'PoolTournamentMatchUser'
has_many :users, through: :pool_tournament_match_users

user.rb

has_many :pool_tournament_users, class_name: 'PoolTournamentUser'
has_many :pool_tournaments, through: :pool_tournament_users

has_many :pool_tournament_match_users, class_name: 'PoolTournamentMatchUser'
has_many :pool_tournament_matches, through: :pool_tournament_match_users

这里有两个通过关联的has_many。一个是在user和pool_tournament之间。另一个在pool_tournament_match和user之间。

我的查询是找出哪个pool_tournament_matches只有一个用户。我的查询为我提供了匹配列表，但它为每个N+1查询了一个pool_tournament_match。

tournament.pool_tournament_matches.includes(:users).select { |m| m.users.count == 1 }

PoolTournamentMatch Load (0.6ms) SELECT "pool_tournament_matches".* FROM "pool_tournament_matches" WHERE "pool_tournament_matches"."pool_tournament_id" = $1 [["pool_tournament_id", 2]] PoolTournamentMatchUser Load (0.6ms) SELECT "pool_tournament_match_users".* FROM "pool_tournament_match_users" WHERE "pool_tournament_match_users"."pool_tournament_match_id" IN (1, 2, 3, 4) User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3, 4, 5, 6, 7, 8) (0.8ms) SELECT COUNT(*) FROM "users" INNER JOIN "pool_tournament_match_users" ON "users"."id" = "pool_tournament_match_users"."user_id" WHERE "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 1]] (0.7ms) SELECT COUNT(*) FROM "users" INNER JOIN "pool_tournament_match_users" ON "users"."id" = "pool_tournament_match_users"."user_id" WHERE "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 2]] (0.7ms) SELECT COUNT(*) FROM "users" INNER JOIN "pool_tournament_match_users" ON "users"."id" = "pool_tournament_match_users"."user_id" WHERE "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 3]] (0.7ms) SELECT COUNT(*) FROM "users" INNER JOIN "pool_tournament_match_users" ON "users"."id" = "pool_tournament_match_users"."user_id" WHERE "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 4]]

我也不介意使用原始SQL，如果需要的话可以发布模式。

谢谢!

Answer 1

您可以让SQL为您进行计数。以下内容应适用于Postgres (不确定其他数据库)：

tournament.pool_tournament_matches
  .select("pool_tournament_matches.*, COUNT(users.id) as user_count")
  .joins("LEFT OUTER JOIN pool_tournament_match_users ON (pool_tournament_match_users.pool_tournament_match_id = pool_tournament_matches.id)")
  .joins("LEFT OUTER JOIN users ON (pool_tournament_match_users.user_id = users.id)")
  .group("pool_tournament_matches.id")
  .select { |match| match.user_count > 0 }

与.group相关的所有内容都会产生一个查询，并将一个'user_count‘属性附加到它返回的pool_tournament_matches。因此，最终的.select (发生在内存中)在不执行其他数据库调用的情况下解析结果。