@Where clause whith nested association

Hibernate ORM
1

I have the following relationship:

@Entity
public class User {

   //other fields

   @Where(clause = "media.deleted = false")
   @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
   private List<Favorite> favorites = new ArrayList<>();

}

@Entity
public class Favorite {

    //other fields

    @ManyToOne(optional = false)
    @JoinColumn(name = "mediaId", nullable = false)
    private Media media;

    @CreatedBy
    @ManyToOne(optional = false)
    @JoinColumn(name = "userId", nullable = false)
    private User user;
}

@Entity
public class Media {

    //other fields

    @Column(columnDefinition = "bool default false")
    private boolean deleted;
}

With @Where(clause = “media.deleted = false”) hibernate generates following query:

select
        f1_0.userId,
        ...
        m1_0.xxx,    
        u1_0.viewsCount,
        m1_0.viewsCount 
    from
        favorites f1_0 
    left join
        medias m1_0 
            on m1_0.id=f1_0.mediaId 
    left join
        users u1_0 
            on u1_0.id=m1_0.userId 
    where
        f1_0.userId=? 
        and (
            **media.deleted = 0**
        )

Resulting in SQL Error: Unknown column ‘media.deleted’ in ‘where clause’

When i change the clause to m1_0.deleted = false it works!

But i suspect the generated alias to not be fixed and change depending on DB or OS.

So How to infer the generated hibernate table alias name in the @Where clause?

2

You can’t use @Where for this sort of use case, because the predicate only applies to the join of the association. If you were using a @JoinTable instead of the Favorite entity, then you could use it.
The only other way to model this is by writing a proper query e.g.

select u, f, m
from User u
join u.favorites f
join f.media m
where m.deleted = false
3

Thank you for your reply
@beikov although as m1_0.deleted = false works i think it is relevant to allow it. maybe a feature request to enhance hibernate resulting in something like

{Media}.deleted = false to automatically peak the generated alias

4

It’s not sensible to “allow” this and it is not implementable generally. Hibernate ORM has no way to know which table you mean if e.g. it involves the same table twice. Using HQL is the solution.