# @Where clause whith nested association

**URL:** <https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071>\
**Category:** Hibernate ORM\
**Created:** [February 21, 2024, 7:06am UTC](https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071 "2024-02-21T07:06:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sdsds89](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/sdsds89/32/2488_2.png) [@sdsds89](https://discourse.hibernate.org/u/sdsds89)\
**Post date:** [February 21, 2024, 7:06am UTC](https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071/1 "2024-02-21T07:06:56Z")

</div>

I have the following relationship:

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

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

---

<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:** [February 21, 2024, 9:22am UTC](https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071/2 "2024-02-21T09:22:38Z")

</div>

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.

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

```

---

<div class="post-metadata">

**Author:** ![sdsds89](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/sdsds89/32/2488_2.png) [@sdsds89](https://discourse.hibernate.org/u/sdsds89)\
**Post date:** [February 22, 2024, 4:23pm UTC](https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071/3 "2024-02-22T16:23:15Z")

</div>

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

---

<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:** [February 26, 2024, 8:51am UTC](https://discourse.hibernate.org/t/where-clause-whith-nested-association/9071/4 "2024-02-26T08:51:38Z")

</div>

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.