ruby-on-railsrubyactiverecord

ActiveRecord query for unassociated has_many through records


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'

Solution

  • My specs pass with this version of the query:

    players.where.not(id: teams.joins(:team_memberships).select('team_memberships.user_id'))