# Apply filter to many-to-one association

**URL:** <https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757>\
**Category:** Hibernate ORM\
**Created:** [December 27, 2023, 9:56am UTC](https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757 "2023-12-27T09:56:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vschuurhof](https://avatars.discourse-cdn.com/v4/letter/v/46a35a/32.png) [@vschuurhof](https://discourse.hibernate.org/u/vschuurhof)\
**Post date:** [December 27, 2023, 9:56am UTC](https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757/1 "2023-12-27T09:56:48Z")

</div>

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](https://forum.hibernate.org/viewtopic.php?p=2355465)):

> 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

```auto
@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

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

```

# SQL (current)

```auto
    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)

```auto
    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 

```

---

<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:** [January 4, 2024, 11:16am UTC](https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757/2 "2024-01-04T11:16:17Z")

</div>

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

```
@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](https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features)

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

```

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

---

<div class="post-metadata">

**Author:** ![vschuurhof](https://avatars.discourse-cdn.com/v4/letter/v/46a35a/32.png) [@vschuurhof](https://discourse.hibernate.org/u/vschuurhof)\
**Post date:** [January 5, 2024, 12:09pm UTC](https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757/3 "2024-01-05T12:09:14Z")

</div>

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?

---

<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:** [January 5, 2024, 2:20pm UTC](https://discourse.hibernate.org/t/apply-filter-to-many-to-one-association/8757/4 "2024-01-05T14:20:24Z")

</div>

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 😉