title: Hibernate Jpa custom query
body: <p>I'm facing a problem that makes me sweat.</p>
  
  <p>first pojo:</p>
  
  <pre><code>@Entity
  @Table(name = "generic_params")
  @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
  @DiscriminatorColumn(name = "type", discriminatorType = DiscriminatorType.STRING)
  @JsonTypeInfo(use = JsonTypeInfo.Id.NAME, include = JsonTypeInfo.As.PROPERTY, property = "type", defaultImpl = Void.class)
  public class GenericParams extends AbstractParam {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @Column(name = "code", unique = true)
      private String code;
  
      @Column(name = "type", insertable = false, updatable = false, unique = true)
      private String type;
  
      @JsonManagedReference
      @OneToMany(mappedBy = "genericParams", cascade = { CascadeType.ALL }, fetch = FetchType.EAGER)
      private Set&lt;LabelTranslation&gt; labelTranslations = new HashSet&lt;&gt;();
      ...getter / setter
  </code></pre>
  
  <p>second pojo:</p>
  
  <pre><code>@Entity
  @Table(name = "label_translation")
  public class LabelTranslation extends AbstractParam {
  
      private static final long serialVersionUID = 1L;
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @Column(name = "label")
      private String label;
  
      @ManyToOne
      private Language language;
  
      @Column(name = "language_id", insertable = false, updatable = false)
      private Long languageId;
  
      @ManyToOne(fetch = FetchType.LAZY)
      @JsonBackReference
      private GenericParams genericParams;
  
      @Column(name = "generic_params_id", insertable = false, updatable = false)
      private Long genericParamsId;
  </code></pre>
  
  <p>GenericParamsMapper</p>
  
  <pre><code>@Mapper(componentModel = "spring", uses = { LabelTranslationMapper.class })
  public interface GenericParamsMapper extends GenericParamsParameterizedMapper&lt;GenericParams&gt; {
  
      @Override
      GenericParamsDTO toDto(GenericParams genericParams);
  
      @Override
      GenericParams toEntity(GenericParamsDTO genericParamsDTO);
  
      @Override
      GenericParams fromSuperClassToDerived(GenericParams genericParams);
  }
  </code></pre>
  
  <p>The problem when I make the request to return a type of GenericParams it returns all LabelTranslation without filter.
  My request:</p>
  
  <pre><code>@Query(value = " SELECT gparam FROM  GenericParams gparam  " //
      + " INNER JOIN gparam.labelTranslations label with label.languageId =:lang " //
      + " WHERE gparam.type =:type " //
      + " AND   gparam.deleted = false ") //
      List&lt;GenericParams&gt; getGenericParamByFilteringLabelTranslation(@Param("lang") Long lg, @Param("type") String tp);
  </code></pre>
  
  <p>this request is called by :</p>
  
  <pre><code>@Override
  public List&lt;GenericParamsDTO&gt; getGenericParamsByType(String type, HttpServletRequest request) {
      Long idLang = this.languageService.findIdByCulture(request.getLocale().toLanguageTag());
      List&lt;GenericParams&gt; gList = this.genericParamsRepository.getGenericParamByFilteringLabelTranslation(idLang, type);
      result this.genericParamsMapper.toDto(gList);
  }
  </code></pre>
  
  <p>Here it is supposed to retrieve a list of GenericParams with the filter on LabelTranslation which have the 2L language for example<br>
  except that here it returns all LabelTranslation  without filter.<br>
  You'll say yes because the mapper ignores the property.<br>
  I will tell you that I need to ignore it for other need.<br>
  So how do i ignore it for one need and filter it for another.<br>
  Getter and setter are omitted in order to simplify<br>
  thanks for lending me a hand.  </p>
  
  <p>it was more challenging than I thought
  by analyzing the queries executed by hibernate, I realized the error.
  despite that i don't know how to solve it.</p>
  
  <p>This first request is good:</p>
  
  <pre><code>  select gp.* from generic_params gp 
      inner join label_translation labeltrans1_ 
      on gp.id=labeltrans1_.generic_params_id and (labeltrans1_.language_id=? and labeltrans1_.language_id=?) 
      where gp.type=? 
      and gp.is_deleted=0;  
  </code></pre>
  
  <p>but this returns the false information (since it is executed automatically by Hibernate)
  so it's a design problem  </p>
  
  <pre><code>select * from label_translation lb 
  left outer join lang lang on lb.language_id=lang.id 
  where lb.generic_params_id=? 
  </code></pre>
  
  <p>now how do i tell hibernate to execute a request like this?  </p>
  
  <pre><code>select label.* from label_translation label 
  left outer join lang langue on label.language_id=langue.id 
  where label.generic_params_id=200
  and langue.id=2;  
  </code></pre>
  
  <p>you will notice that I just added this piece of code:</p>
  
  <pre><code>and langue.id=2;
  </code></pre>
  
  <p>So to make a summary, I left things as they are and I added in the mapper a method that filters on LabelTransaltion and I have the right result.<br>
  a second option is to make two requests:<br>
  - The first one returns the GenericParams<br>
  - The second one returns the LabelTransaltion and then in the GenericParams object i set the list of LabelTransaltion by the 2nd request.<br>
  <strong>But I wonder if there is another more concise hibernate way.</strong><br>
  thank you  </p>