Sean Hawkridge Sean Hawkridge - 1 year ago 46
SQL Question

Rails - scope for records that are not in a join table alongside a specific association

I have two models in a Rails app -

associated through a join table:

class Tournament < ApplicationRecord

has_many :tournament_players
has_many :players, through: :tournament_players


class Player < ApplicationRecord

has_many :tournament_players
has_many :tournaments, through: :tournament_players

scope :selected, -> (tournament) { includes(:tournaments).where(tournaments: {id:}) }


I have lots of Tournaments, and each one can have lots of Players. Players can play in lots of Tournaments. The scope

scope :selected, -> (tournament) { includes(:tournaments).where(tournaments: {id:}) }

successfuly finds all the players already added to a tournament, given that tournament as an argument.

What I'd like is a scope that does the opposite - returns all the players not yet added to a given tournament. I've tried

scope :not_selected, -> (tournament) { includes(:tournaments).where.not(tournaments: {id:}) }

but that returns many of the same players, I think because the players exist as part of other tournaments. The SQL for that looks something like:

SELECT "players".*, "tournaments”.* FROM "players" LEFT OUTER JOIN
"tournament_players" ON "tournament_players"."player_id" =
"players"."id" LEFT OUTER JOIN "tournaments" ON "tournaments"."id" =
"tournament_players"."tournament_id" WHERE ("tournaments"."id" != $1)
ORDER BY "players"."name" ASC [["id", 22]]

I've also tried the suggestions on this question - using

scope :not_selected, -> (tournament) { includes(:tournaments).where(tournaments: {id: nil}) }

but that doesn't seem to work - it just returns an empty array, again I think because the Players exist in the join table as part of a separate Tournament. The SQL for that looks something like:

SELECT "players”.*, "tournaments”.* FROM "players" LEFT OUTER JOIN
"tournament_players" ON "tournament_players"."player_id" =
"players"."id" LEFT OUTER JOIN "tournaments" ON "tournaments"."id" =
"tournament_players"."tournament_id" WHERE "tournaments"."id" IS NULL
ORDER BY "players"."name" ASC

Answer Source

What you need to do is:

  1. Make a left join with the reference table, with an additional condition on the tournament ID matching the one that you want to find the not-selected players for
  2. Apply a WHERE clause indicating that there was no JOIN made.

This code should do it:

# player.rb
scope :not_selected, -> (tournament) self.find_not_in_tournament(tournament_id)
  joins("LEFT JOIN tournament_players tp ON = tp.player_id AND tp.tournament_id = #{}").where(tp: {tournament_id: nil})

If only Rails had a nicer way to write LEFT JOIN queries with additional conditions...

A few notes:

  1. Don't join the actual relation (i.e. Tournament), it dramatically decreases performance of your query, and it's unnecessary, because all your condition prerequisites are inside the reference table. Besides, all the rows you're interested in return NULL data from the tournaments table.
  2. Don't use eager_load. Besides to my best knowledge its not supporting custom conditions, it would create models for all related objects, which you don't need.