CODEWITHSUNDEEP
javahibernatejpa
Thilo-Alexander Ginkel
Thilo-Alexander Ginkel

Reputation: 6958

Finding all JPA entities that are not referenced from another entity

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?

Upvotes: 3

Views: 2160

Answers (4)

Steve Ebersole
Steve Ebersole

Reputation: 9443

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

Upvotes: 6

esej
esej

Reputation: 3059

The EntityB doesn't know about which EntityA it belongs to. There might be some obscure or even some intuitive way of doing what you want to do in JPQL. But from my point of view you have failed to model your object model if you want to ask such questions.

"Hey B, what is your A - or is it non-exitent" Will get answered by: "What is "A"".

Adding information about its apparent relationship to A in B, will quickly resolve your current design problem.

Upvotes: 0

Sai Ye Yan Naing Aye
Sai Ye Yan Naing Aye

Reputation: 6738

Please try this,

    SELECT DISTINCT b FROM EntityB AS b JOIN b.a AS a WHERE a IS NULL

If you can't, try that

    SELECT DISTINCT b FROM EntityB AS b JOIN b.a AS a WHERE a.id IS NULL

Upvotes: 0

Korgen
Korgen

Reputation: 5399

what do you mean by invalid SQL? did you actually try ...WHERE b NOT IN... (note the position of the NOT changed)

Upvotes: 0

Related Questions