sql - How do I get this query result in a single query instead of N+1 -

a pool_tournament has many pool_tournament_matches , each match belongs multiple users. user 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 

there 2 has_many through associations here. 1 between user , pool_tournament. other between pool_tournament_match , user.

my query figure out pool_tournament_matches have 1 user. query got me list of matches it's doing n+1 query each pool_tournament_match.

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

pooltournamentmatch load (0.6ms) select "pool_tournament_matches".* "pool_tournament_matches" "pool_tournament_matches"."pool_tournament_id" = $1 [["pool_tournament_id", 2]] pooltournamentmatchuser load (0.6ms) select "pool_tournament_match_users".* "pool_tournament_match_users" "pool_tournament_match_users"."pool_tournament_match_id" in (1, 2, 3, 4) user load (0.6ms) select "users".* "users" "users"."id" in (1, 2, 3, 4, 5, 6, 7, 8) (0.8ms) select count(*) "users" inner join "pool_tournament_match_users" on "users"."id" = "pool_tournament_match_users"."user_id" "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 1]] (0.7ms) select count(*) "users" inner join "pool_tournament_match_users" on "users"."id" = "pool_tournament_match_users"."user_id" "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 2]] (0.7ms) select count(*) "users" inner join "pool_tournament_match_users" on "users"."id" = "pool_tournament_match_users"."user_id" "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 3]] (0.7ms) select count(*) "users" inner join "pool_tournament_match_users" on "users"."id" = "pool_tournament_match_users"."user_id" "pool_tournament_match_users"."pool_tournament_match_id" = $1 [["pool_tournament_match_id", 4]]

i don't mind using raw sql , can post schema if needed.

thanks!

you can have sql counting you. following should work in postgres (not sure other databases):

tournament.pool_tournament_matches   .select("pool_tournament_matches.*, count(users.id) 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 , incuding .group produces single query, , attaches 'user_count' attribute pool_tournament_matches returns. final .select, happens in memory, parses on result without doing additional database calls.