title: Spring Data findAll with filtered nested objects
body: <p>I am currently developing a Spring Boot 2.5.5 app which needs to integrate some games. Each <code>Game</code> entity contains multiple <code>GameProfile</code> entities. Since the app and games are multilingual, we store all the generic fields in the <code>GameProfile</code> entity and all the others which are tied to the current language, are stored in an extra relation called <code>GameProfileTranslation</code> which references a <code>Language</code> entity.</p>
  <p>Following are the three entities:</p>
  <pre><code>@Entity
  @Table(name = &quot;GAME_PROFILE&quot;)
  @Getter
  @Setter
  public class GameProfile {
  
      @Id
      @Column(name = &quot;GAME_PROFILE_ID&quot;)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;game_profile_seq&quot;)
      @SequenceGenerator(name = &quot;game_profile_seq&quot;, allocationSize = 1, sequenceName=&quot;game_profile_sequence&quot;)
      private Long id;
  
      @Column(name = &quot;IMAGE&quot;)
      private String image;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = &quot;GAME_ID&quot;)
      private Game game;
  
      @Column(name = &quot;DIFFICULTY&quot;)
      private GameDifficulty difficulty;
  
      @OneToMany(mappedBy = &quot;gameProfile&quot;, fetch = FetchType.LAZY)
      private Set&lt;GameProfileTranslation&gt; translations = new HashSet&lt;&gt;();
  
  }
  </code></pre>
  <pre><code>@Entity
  @Table(name = &quot;GAME_PROFILE_TRANSLATION&quot;)
  @Getter
  @Setter
  public class GameProfileTranslation {
  
      @Id
      @Column(name = &quot;GAME_PROFILE_TRANSLATION_ID&quot;)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;game_profile_translation_seq&quot;)
      @SequenceGenerator(name = &quot;game_profile_translation_seq&quot;, allocationSize = 1, sequenceName=&quot;game_profile_translation_sequence&quot;)
      private Long id;
  
      @Column(name = &quot;TITLE&quot;)
      private String title;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = &quot;GAME_PROFILE_ID&quot;)
      private GameProfile gameProfile;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JoinColumn(name = &quot;LANGUAGE_ID&quot;)
      private Language language;
  }
  </code></pre>
  <pre><code>
  @Entity
  @Table(name = &quot;LANGUAGE&quot;)
  @Getter
  @Setter
  @NoArgsConstructor
  public class Language {
  
      @Id
      @Column(name = &quot;LANGUAGE_ID&quot;)
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @Column(name = &quot;CODE&quot;)
      private String code;
  
      @Column(name = &quot;VALUE&quot;)
      private String value;
  
      @OneToMany(mappedBy = &quot;language&quot;, fetch = FetchType.LAZY)
      private Set&lt;User&gt; users = new HashSet&lt;&gt;();
  
      @OneToMany(mappedBy = &quot;language&quot;, fetch = FetchType.LAZY)
      private Set&lt;GameProfileTranslation&gt; gameProfileTranslations = new HashSet&lt;&gt;();
  
  }
  
  </code></pre>
  <p>For the past two days I have been trying to create a repository method which returns all the <code>GameProfile</code> entities based on a <code>languageId</code> and a <code>gameId</code> given as <code>@RequestParam</code> to my Controller. To be more precise, I want to find all the <code>GameProfiles</code> and filter the nested <code>translations</code> object based on the language given.</p>
  <p>I have tried two approaches, which are presented below. Both of these methods actually return all the <code>GameProfile</code> entities but the nested <code>translations</code> object contains all the <code>GameProfileTranslation</code> entities in the database. It is like the <code>translations</code> cannot be filtered by the language.</p>
  <p>If I copy &amp; 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.</p>
  <p>My two approaches:</p>
  <pre><code>    @Query(
          nativeQuery = true,
          value = &quot;SELECT * &quot; +
                  &quot;FROM game_profile AS gp &quot; +
                  &quot; INNER JOIN game_profile_translation AS gpt ON gp.game_profile_id = gpt.game_profile_id &quot; +
                  &quot; INNER JOIN language AS l ON gpt.language_id = l.language_id &quot; +
                  &quot;WHERE l.language_id = :languageId AND gp.game_id = :gameId &quot;)
      List&lt;GameProfile&gt; findAllByGameIdAndLanguageId(@Param(&quot;gameId&quot;) Long gameId, @Param(&quot;languageId&quot;) Long languageId);
  
  </code></pre>
  <p>and</p>
  <pre><code>    @Query(value =  &quot;SELECT gp &quot; +
                      &quot;FROM GameProfile AS gp JOIN FETCH gp.translations AS gpt &quot; +
                      &quot;WHERE gp.game.id = :gameId AND gpt.language.id = :languageId &quot;)
      List&lt;GameProfile&gt; findAllByGameIdAndLanguageId(@Param(&quot;gameId&quot;) Long gameId, @Param(&quot;languageId&quot;) Long languageId);
  </code></pre>
  <p>TLDR: Both of these methods return all the <code>GameProfile</code> entites without filtering the nested <code>translations</code> object. To be more exact, the <code>translations</code> field contains all the translations available in the database regardless of the <code>languageId</code>.</p>
  <p>Is there a way to return a <code>List&lt;GameProfile&gt;</code> entities with the <code>translations</code> object filtered by the <code>languageId</code>?</p>
  <p>What I have tried:</p>
  <ol>
  <li><a href="https://stackoverflow.com/questions/26684361/filter-child-object-in-spring-data-query">Filter child object in Spring Data Query</a></li>
  <li><a href="https://stackoverflow.com/questions/63266634/filtering-out-nested-objects-in-jpa-query">Filtering out nested objects in JPA query</a></li>
  <li><a href="https://stackoverflow.com/questions/26684361/filter-child-object-in-spring-data-query">Filter child object in Spring Data Query</a></li>
  </ol>