Apply filter to many-to-one association

Hibernate ORM
1

I have 2 entities as described below. What I would like to achieve is to fetch the related entity in a single query, but only the ones with a specific status. This can be achieved by adding and identity1_.status_id=1 to the JOIN of the SQL query.

I found that additional conditions with ON or WITH are supported for a JOIN but not for a JOIN FETCH. Instead, I get the message with-clause not allowed on fetched associations; use filters. So I tried filters and found it to be working for one-to-one and one-to-many associations but not for many-to-one. Besides, using filters in this case feels very cumbersome instead of just being able to extend the query with the desired conditions.

Christian Bauer stated many years ago (Hibernate Community • View topic - Filters on Many-To-One association):

Filters should (not work at all) on a many-to-one, because that is conceptually impossible (the only thing a filter can do is return nothing, which violates the cardinality of the association, it would turn into a many-to-zero-or-one).

First of all, I don’t understand what is so wrong about this concept? Moreover, I don’t see any other way of getting my use case to work.

So, any ideas on how to solve my issue?

Entities

@Entity
public class Role {

    @Id
    @Column(name = "id", unique = true)
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "owner_identity_id")
    private Identity owner;
}

@Entity
public class Identity {

    @Id
    @Column(name = "id", unique = true)
    private Integer id;

    @Column(name = "status_id")
    private Integer statusId;
    
    @OneToMany(mappedBy = "owner")
    private List<Role> ownedRoles;
}

JPQL

SELECT role
FROM Role AS role
LEFT JOIN FETCH role.owner

SQL (current)

    select
        role0_.id as id1_32_0_,
        identity1_.id as id1_24_1_,
        role0_.owner_identity_id as owner_id4_32_0_,
        identity1_.status_id as status_id53_24_1_
    from
        role role0_ 
    left outer join
        identity identity1_ 
            on role0_.owner_identity_id=identity1_.identity_id

SQL (required)

    select
        role0_.id as id1_32_0_,
        identity1_.id as id1_24_1_,
        role0_.owner_identity_id as owner_id4_32_0_,
        identity1_.status_id as status_id53_24_1_
    from
        role role0_ 
    left outer join
        identity identity1_ 
            on role0_.owner_identity_id=identity1_.identity_id 
            and identity1_.status_id=1
2

The solution is to use a DTO model and 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:

@EntityView(Role.class)
public interface RoleDto {
    @IdMapping
    Integer getId();
    String getName();
    @Mapping("owner[statusId = 1]")
    IdentityDto getActiveOwner();

    @EntityView(Identity.class)
    interface IdentityDto {
        @IdMapping
        Long getId();
        Integer getStatusId();
    }
}

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

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

The Spring Data integration allows you to use it almost like Spring Data Projections: Blaze Persistence - Entity View Module

Page<RoleDto> findAll(Pageable pageable);

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

3

Thanks for your answer, Christian. However, this would mean more efforts to be put into yet another framework.

I would assume that Hibernate itself would be able to solve such an obvious use case, given its history and magnitude? Any thoughts on this?

4

Of course there is a plain solution. Write an HQL query that doesn’t use join fetch but selects all data instead and map the result to objects by hand :wink: