Multiple @ElementCollection fields with discriminator?

I want to have a Game entity that has two List of player IDs stored as Longs

This is my table: game_player_team

game_id	player_id	team
1	1	H
1	2	H
1	3	A
1	4	A

And this is how far I got modeling the Game entity

I can’t work out how to get only the player_id’s for the team H row and player_id’s for the team A rows in to homePlayerIds and awayPlayerIds respectively.

I’ve seen the @Discriminator annotations but they only seem to work inheritence.

@Entity
class Game {
    
    @Id
    private Long id

    @ElementCollection
    @CollectionTable(name="game_player_team", joinColumns={@JoinColumn(name="game_id")})
    @Column(name="player_id")
    private List<Long> homePlayerIds;

    @ElementCollection
    @CollectionTable(name="game_player_team", joinColumns={@JoinColumn(name="game_id")})
    @Column(name="player_id")
    private List<Long> awayPlayerIds;


}

Answer

You can use the @Where annotation, but because you are using @CollectionTable, you need to change the mapping a bit.

This mapping will work:

    @Entity
    class Game {

        @Id
        public Long id;

        @ElementCollection
        @CollectionTable(name="game_player_team", joinColumns={@JoinColumn(name="game_id")})
        @Column(name="player_id")
        @Where(clause = "team = 'H'")
        public List<Player> homePlayerIds;

        @ElementCollection
        @CollectionTable(name="game_player_team", joinColumns={@JoinColumn(name="game_id")})
        @Where(clause = "team = 'A'")
        @Column(name="player_id")
        public List<Player> awayPlayerIds;
    }

    @Embeddable
    class Player {
        @Column(name = "player_id")
        public Long playerId;
        public String team;

        public Player() {
        }

        public Player(Long playerId, String team) {
            this.playerId = playerId;
            this.team = team;
        }
    }

Note that for associations, one would normally use @WhereJoinTable but, when I’ve tested it, it didn’t work with @CollectionTable.