ActiveRecord查询问题：获取指定赛季无球队球员失败

需求目标 #

获取单个指定赛季内未加入任何球队的球员：

• 包含其他赛季有球队，但当前赛季无球队的球员
• 排除当前赛季已加入球队的球员

问题现象 #

原本查询逻辑能通过测试，但在当前赛季新增一支球队后，查询返回了所有球员（包括当前赛季已加入球队的球员），不符合预期。

相关代码 #

异常查询方法 #

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
end

失败测试用例 #

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 # 已注册赛季但未加入球队
    team
    team_membership

    # 验证其他赛季有球队的球员在当前赛季仍被视为无球队球员
    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)

    # 移除该行测试可通过：当前赛季新增另一支球队
    FactoryBot.create(:team, season:)

    expect(season.no_team_players).to eq([no_team_player])
  end
end

问题原因 #

原查询逻辑通过LEFT JOIN teams关联当前赛季所有球队，再关联team_memberships。当当前赛季有多支球队时，已加入球队的球员会与非所属球队产生无关联的记录（即team_memberships.user_id IS NULL），最终被WHERE条件选中，DISTINCT也无法过滤掉该球员。

修复方案 #

使用NOT EXISTS子查询直接检查球员在当前赛季是否存在任何球队关联，逻辑更清晰且避免多球队导致的错误：

class Season < ApplicationRecord
  ...
  def no_team_players
    players.where(
      'NOT EXISTS (
        SELECT 1 FROM team_memberships
        JOIN teams ON team_memberships.team_id = teams.id
        WHERE team_memberships.user_id = users.id
        AND teams.season_id = ?
      )', id
    )
  end
end

也可以用Rails关联查询的写法，更符合ActiveRecord风格：

def no_team_players
  players.where.not(
    id: TeamMembership.joins(:team).where(teams: { season_id: id }).select(:user_id)
  )
end

两种写法都能精准筛选出当前赛季无任何球队关联的球员，新增球队后测试用例可正常通过。

环境信息 #

• Ruby 3.2.2
• Rails ~> 7.1.2

内容的提问来源于stack exchange，提问作者emersonthis