Finding all JPA entities that are not referenced from another entity
hibernate, java, jpa
Solution
Weird that this query does not give you an exception. But your b.a reference is not legal in this way; you cannot select the collection itself, you want/need to select the collection elements.
SELECT b FROM EntityB b WHERE b NOT IN (SELECT DISTINCT elements(a.b) FROM EntityA a)
Note however that elements is a Hibernate HQL construct, not JPA.
If you care about JPA portability:
SELECT b FROM EntityB b WHERE b NOT IN (SELECT DISTINCT b2 FROM EntityA a join a.b b2)
* NOT IN is the more normal form, so I used that here as well
Problem
Let's say I have two JPA entities: ``` @Entity public class EntityA { @Id private String id; @OneToMany private List<EntityB> b; ... } @Entity public class EntityB { @Id private String id; ... } ``` What I would like to accomplish is to define a `@NamedQuery` that retrieves all `EntityB` instances that are not referenced from any `EntityA`. Naively, I would have tried ``` @NamedQuery(name = "EntityB.findAllUnassigned", query = "SELECT b FROM EntityB b WHERE NOT b IN (SELECT DISTINCT a.b FROM EntityA a)") ``` but this ends up generating invalid SQL: ``` select b0_.id as id1_, b0_.attr, ... from b b0_ where b0_.id not in (select distinct . from a a1_, a_b ab2_, b b3_ where a1_.id=ab2_.a_id and ab2_.b_id=b3_.id) ``` Any alternatives?