The goal: I want to return all the players who are NOT members of a team.
The challenge: I need the results scoped to an individual league/season. My application supports multiple leagues and seasons. So the query must include a player that IS on a team for a different league/season, but exclude any player that is on a team that belongs to the relevant Season instance.
In the spec below, I create two users/players. I assign one to a team of the relevant season. The other is not associated with a team. The spec was passing before I added an additional team to the season. With this additional record, the query returns BOTH users.
What am I doing wrong?
The query
(This is NOT quite working yet)
class Season < ApplicationRecord
...
def no_team_players
players
.joins("LEFT JOIN teams ON teams.season_id = #{id}")
.joins(
'LEFT JOIN team_memberships ON users.id = team_memberships.user_id AND teams.id = team_memberships.team_id'
)
.where('team_memberships.user_id IS NULL')
.distinct
end
The failing spec
describe '#no_team_players' do
let(:team_player) { FactoryBot.create(:user, email: 'team_player@gmail.com') }
let(:no_team_player) { FactoryBot.create(:user, email: 'no_team_player@gmail.com') }
let(:season) { FactoryBot.create(:season) }
let(:team_player_enrollment) { FactoryBot.create(:enrollment, user: team_player, season:) }
let(:no_team_player_enrollment) do
FactoryBot.create(:enrollment, user: no_team_player, season:)
end
let(:team) { FactoryBot.create(:team, season:) }
let(:team_membership) { FactoryBot.create(:team_membership, user: team_player, team:) }
it 'returns the players for the season that do not have a team membership for that specific season' do
team_player_enrollment
no_team_player_enrollment # enrolled in the season but not on a team
team
team_membership
# The following is necessary to make sure that a user can be on a team for a different season
# and still be considered a player without a team for the current season.
# Here we create a different season and a team for that season and add the no_team_player to that team.
different_season = FactoryBot.create(:season)
FactoryBot.create(:enrollment, user: no_team_player, season: different_season)
different_season_team = FactoryBot.create(:team, season: different_season)
FactoryBot.create(:team_membership, user: no_team_player, team: different_season_team)
# THE SPEC PASSES IF THIS IS REMOVED
# another team in this season
FactoryBot.create(:team, season:)
expect(season.no_team_players).to eq([no_team_player])
end
end
Right now the method is return BOTH User records (team_player
and no_team_player
).
Details
ruby '3.2.2'
gem 'rails', '~> 7.1.2'
My specs pass with this version of the query:
players.where.not(id: teams.joins(:team_memberships).select('team_memberships.user_id'))