@ManyToOne left join with custom condition

Hibernate ORM
1

This is my schema:

7k3mS1
7k3mS1846×284 5.34 KB

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:

@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

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?

       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).

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

2

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.

3

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.

4

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.

5

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

6

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.

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

Page<PostDto> findAll(Pageable pageable);

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