@Where clause whith nested association

I have the following relationship:

public class User {

   //other fields

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


public class Favorite {

    //other fields

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

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

public class Media {

    //other fields

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

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

        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 
        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?

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

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

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.