# @ManyToOne left join with custom condition

**URL:** <https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444>\
**Category:** Hibernate ORM\
**Created:** [March 27, 2023, 10:35am UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444 "2023-03-27T10:35:07Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Emax](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/emax/32/2345_2.png) [@Emax](https://discourse.hibernate.org/u/Emax)\
**Post date:** [March 27, 2023, 10:35am UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/1 "2023-03-27T10:35:08Z")

</div>

This is my schema:

 ![7k3mS1](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/e/e1de5a34947b0922ebd5b92520222b26f55aca61.png)

I have a post entity containing a title column that have a one-to-one relationship with the translation keys. Translation keys have many values (one per language).

This my post entity:

```auto
@Entity
@Table(name = "posts")
@NamedQuery(name = "Post.findAll", query = "SELECT p FROM Post p")
public class Post {

	@Column(name = "identifier")
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer identifier;

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "name")
	private TranslationKey name;

	@ManyToOne(fetch = FetchType.EAGER)
	@Fetch(FetchMode.JOIN)
	@JoinColumn(name = "name", referencedColumnName = "key", insertable = false, updatable = false)
	private TranslationValue nameTranslation;

	// Getters & Setters
}

```

When I normally select the entity from the database, Hibernate generates a left join

```auto
SELECT p1_0.`identifier`,
       p1_0.`name`,
       n1_0.`identifier`,
       n1_0.`key`,
       n1_0.`language`,
       n1_0.`value`
FROM `posts` p1_0
       LEFT JOIN `translations_values` n1_0
              ON n1_0.`key` = p1_0.`name`
WHERE p1_0.`identifier` =? 

```

Now since there are multiple translations (translations\_values) for the same key an error is generated (Duplicate row was found). My question is would it be possible to add the condition (language = :language) to the generated left join?

```auto
       LEFT JOIN `translations_values` n1_0
              ON n1_0.`key` = p1_0.`name`
             AND n1_0.`language` = :something_decided_in_runtime

```

I was thinking about using a `@Filter` but the documentation states that they can’t be used on `@ManyToOne`. Alternatively a `@JoinColumnOrFormula` but the problem is that they can’t have dynamic values (Though I could use a hack like [this](https://stackoverflow.com/questions/32992465/hibernate-join-annotation-on-dynamic-value)).

I have to load the post entity with translations in a specific language decided in rutime.

I’m aware that I could use a `@OneToMany` with a `@Filter` but I’d rather avoid doing multiple SELECTs when I could just add a condition on the LEFT JOIN

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 27, 2023, 11:58am UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/2 "2023-03-27T11:58:02Z")

</div>

Use a one-to-many with filters or use a DTO approach. The SQL query will be the same. It is just a different/correct entity model.

---

<div class="post-metadata">

**Author:** ![Emax](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/emax/32/2345_2.png) [@Emax](https://discourse.hibernate.org/u/Emax)\
**Post date:** [March 27, 2023, 12:20pm UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/3 "2023-03-27T12:20:14Z")

</div>

In my tests `@OneToMany` always generates two selects (one for the entity and one for filling the values), which is not the best in terms of performance.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 27, 2023, 2:24pm UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/4 "2023-03-27T14:24:36Z")

</div>

What query do you use? You will have to join fetch the association or use an entity graph to tell Hibernate to fetch the collection as part of the main query.

---

<div class="post-metadata">

**Author:** ![Emax](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/emax/32/2345_2.png) [@Emax](https://discourse.hibernate.org/u/Emax)\
**Post date:** [March 27, 2023, 4:16pm UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/5 "2023-03-27T16:16:37Z")

</div>

I’m not using any queries, I’m just annotating the field with `@OneToMany(EAGER)`.  
I would like to avoid writing custom queries because I have many entities and also some localized entities have other localized relationships. For example the post entity has a relationship with the localized categories entity, I would like Hibernate to automatically add the language condition to all the cascading relationships present

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 27, 2023, 4:35pm UTC](https://discourse.hibernate.org/t/manytoone-left-join-with-custom-condition/7444/6 "2023-03-27T16:35:58Z")

</div>

Even then, Hibernate will join fetch the collection if you are using `EntityManager.find()`, but I guess you are using queries without knowing it e.g. through some Spring Data “abstraction”.  
Either way, I would strongly recommend you to use a dedicated query. Then you can control all of that, though it also isn’t very advisable to join fetch and apply a join condition. So IMO your only viable option is to use the one-to-many mapping with a DTO approach:

I think this is a perfect use case for [Blaze-Persistence Entity Views](https://github.com/Blazebit/blaze-persistence#entity-view-usage).

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

```
public interface NameTranslationFacet {
    @Mapping("names[:language]")
    String getName();
}
@EntityView(Post.class)
public interface PostDto extends NameTranslationFacet {
    @IdMapping
    Long getId();
    String getName();
    Set<CategoryDto> getCategories();

    @EntityView(Category.class)
    interface CategoryDto extends NameTranslationFacet {
        @IdMapping
        Long getId();
    }
}

```

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

`PostDto a = entityViewManager.find(entityManager, PostDto.class, id);`

The Spring Data integration allows you to use it almost like Spring Data Projections: [https://persistence.blazebit.com/documentation/entity-view/manual/en\_US/index.html#spring-data-features](https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features)

```
Page<PostDto> findAll(Pageable pageable);

```

The best part is, it will only fetch the state that is actually necessary!