Hibernate @Filter on @ManyToOne

Question

Could some please explain me why Having a @Filter on a @ManyToOne relation does not work? Here i have a really simple example showing this:

I have created two simple table in my database (foo and bar)

Foo: id/id_bar
bar: id/name/state (state can be active or inactive)

and my Entities:

@Entity
@FilterDef(name = "foo_active")
@Table(name = "foo")
public class Foo extends AbstractTimestampEntity implements Serializable {


    private static final long serialVersionUID = 1L;

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

    @ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "bar")
    @Filter(name = "foo_active", condition = "state='active'")
    private Bar bar;
}

@Entity
@Table(name = "foo")
public class Bar extends AbstractTimestampEntity implements Serializable {


    private static final long serialVersionUID = 1L;

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

    @Column(name = "name")
    private String name;

    @Column(name = "state")
    private String state;
}

Now when I run this query

@Query("SELECT f FROM Foo f")
public List<Foo> getTest();

it returns the full content of Foo (active as well as inactive.....) it looks like the filter is not activated even tough i run just before the query

Session session = entityManager.unwrap(Session.class);
session.enableFilter("foo_active")

In the log I can see this:

0:47:33.878 [Test worker] DEBUG o.h.h.i.ast.QueryTranslatorImpl - HQL: SELECT f FROM pl.jcommerce.ocean.services.model.Foo f
00:47:33.878 [Test worker] DEBUG o.h.h.i.ast.QueryTranslatorImpl - S**QL: select foo0_.id as id1_5_, foo0_.created as created2_5_, foo0_.updated as updated3_5_, foo0_.bar as bar6_5_ from foo foo0_**

As you see, there is "WHERE bar.state='active'" clause....

Could some help me with this ?

Thank you!

Answer 1

I faced the same issue with @Filter when using along with @ManyToOne recently. The trick is to use @Filter on the target entity not on the association.

@Entity
@Table(name = "foo")
@Filter(name = "foo_active", condition = "state='active'")
public class Bar

and the association in Foo class should be like:

@ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
@JoinColumn(name = "bar")
private Bar bar;

Hope this helps!

Answer 2

You can try this: instead of the @Filter on the @ManyToOne field, add this filter on class Foo:

@Filter(name = "foo_active", condition = "bar in (select id from Bar where state = 'active')")

Note though that this may result in an inefficient query.