ManyToOne with where clause

Question

I have some logically deleted records (ie active=false) which are causing problems with my @ManyToOne mapping since more than one result is being returned by the join column.

I need to only include records where active=true which I thought I could achieve by:

@ManyToOne
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumn(name = "site_id", referencedColumnName = "site_id", insertable = false, updatable = false)
@WhereJoinTable(clause = "active=true")
private Site site;

However, it seems like the WhereJoinTable is not being used by hibernate (perhaps its only valid for OneToMany?) since the active=true does not show up in the generated SQL (logs) and the problem persists.

Is it possible to include a where clause for the join of a ManyToOne and how?

Answer 1

@WhereJoinTable is not supported with @ManyToOne. There is bug HHH-4335 about subject open since five years. I am not aware about any workaround, except using view (in the case of read-only access) as mentioned in bug report.

Answer 2

@JoinColumnOrFormula anotation is a suitable workaround