0

A pool_tournament has many pool_tournament_matches and each match belongs to multiple users. A user has_many pool_tournaments and 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

There are 2 has_many through associations here. One is between the user and the pool_tournament. The other is between the pool_tournament_match and the user.

My query is to figure out which pool_tournament_matches only have 1 user. My query got me the list of matches but it's doing a N+1 query for each 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]]

I also don't mind using RAW SQL and can post the schema if needed.

Thanks!

oky_sabeni
  • 7,672
  • 15
  • 65
  • 89
  • 1
    deleted my answer because it was incorrect. See http://stackoverflow.com/questions/16348333/rails-includes-with-conditions – max pleaner Nov 06 '16 at 00:58

1 Answers1

1

You can have SQL do the counting for you. The following should work in Postgres (not sure about other databases):

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 }

Everything up to and incuding the .group produces a single query, and it attaches a 'user_count' attribute to the pool_tournament_matches it returns. So the final .select, which happens in memory, parses over the result without doing additional database calls.

moveson
  • 5,103
  • 1
  • 15
  • 32
  • You are a life saver! I will try to understand the query you posted and hope to get better at this! – oky_sabeni Nov 07 '16 at 22:46
  • 1
    I had a nearly identical problem in my own project a few months ago, so I just had to go dig it up and replace my table names with yours. Glad it was helpful. – moveson Nov 07 '16 at 23:05