I am currently developing a Spring Boot 2.5.5 app which needs to integrate some games. Each Game entity contains multiple GameProfile entities. Since the app and games are multilingual, we store all the generic fields in the GameProfile entity and all the others which are tied to the current language, are stored in an extra relation called GameProfileTranslation which references a Language entity.

Following are the three entities:

@Entity
@Table(name = "GAME_PROFILE")
@Getter
@Setter
public class GameProfile {

    @Id
    @Column(name = "GAME_PROFILE_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "game_profile_seq")
    @SequenceGenerator(name = "game_profile_seq", allocationSize = 1, sequenceName="game_profile_sequence")
    private Long id;

    @Column(name = "IMAGE")
    private String image;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "GAME_ID")
    private Game game;

    @Column(name = "DIFFICULTY")
    private GameDifficulty difficulty;

    @OneToMany(mappedBy = "gameProfile", fetch = FetchType.LAZY)
    private Set<GameProfileTranslation> translations = new HashSet<>();

}

@Entity
@Table(name = "GAME_PROFILE_TRANSLATION")
@Getter
@Setter
public class GameProfileTranslation {

    @Id
    @Column(name = "GAME_PROFILE_TRANSLATION_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "game_profile_translation_seq")
    @SequenceGenerator(name = "game_profile_translation_seq", allocationSize = 1, sequenceName="game_profile_translation_sequence")
    private Long id;

    @Column(name = "TITLE")
    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "GAME_PROFILE_ID")
    private GameProfile gameProfile;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "LANGUAGE_ID")
    private Language language;
}

@Entity
@Table(name = "LANGUAGE")
@Getter
@Setter
@NoArgsConstructor
public class Language {

    @Id
    @Column(name = "LANGUAGE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "CODE")
    private String code;

    @Column(name = "VALUE")
    private String value;

    @OneToMany(mappedBy = "language", fetch = FetchType.LAZY)
    private Set<User> users = new HashSet<>();

    @OneToMany(mappedBy = "language", fetch = FetchType.LAZY)
    private Set<GameProfileTranslation> gameProfileTranslations = new HashSet<>();

}

For the past two days I have been trying to create a repository method which returns all the GameProfile entities based on a languageId and a gameId given as @RequestParam to my Controller. To be more precise, I want to find all the GameProfiles and filter the nested translations object based on the language given.

I have tried two approaches, which are presented below. Both of these methods actually return all the GameProfile entities but the nested translations object contains all the GameProfileTranslation entities in the database. It is like the translations cannot be filtered by the language.

If I copy & paste the native query below in a Query Tool in pgAdmin I get the result I desire. However, using it through Spring Data returns all the translations.

My two approaches:

    @Query(
        nativeQuery = true,
        value = "SELECT * " +
                "FROM game_profile AS gp " +
                " INNER JOIN game_profile_translation AS gpt ON gp.game_profile_id = gpt.game_profile_id " +
                " INNER JOIN language AS l ON gpt.language_id = l.language_id " +
                "WHERE l.language_id = :languageId AND gp.game_id = :gameId ")
    List<GameProfile> findAllByGameIdAndLanguageId(@Param("gameId") Long gameId, @Param("languageId") Long languageId);

and

    @Query(value =  "SELECT gp " +
                    "FROM GameProfile AS gp JOIN FETCH gp.translations AS gpt " +
                    "WHERE gp.game.id = :gameId AND gpt.language.id = :languageId ")
    List<GameProfile> findAllByGameIdAndLanguageId(@Param("gameId") Long gameId, @Param("languageId") Long languageId);

TLDR: Both of these methods return all the GameProfile entites without filtering the nested translations object. To be more exact, the translations field contains all the translations available in the database regardless of the languageId.

Is there a way to return a List<GameProfile> entities with the translations object filtered by the languageId?

What I have tried:

1. Filter child object in Spring Data Query
2. Filtering out nested objects in JPA query
3. Filter child object in Spring Data Query