CODEWITHSUNDEEP
hibernatejpajpql
Tonio
Tonio

Reputation: 1556

JPA/Hibernate select element not in collection

If I have two entity classes, Group and Person, where there is a one-to-many relationship between Group and Person (that is, a person can belong to at most one group, a group can have many persons), what jpql/hql query can I use to select all Persons not in Groups?

Something like the reverse of select p from Group g inner join g.people p...

Upvotes: 0

Views: 3277

Answers (2)

Tonio
Tonio

Reputation: 1556

This worked:

select p from Person p where p not in (select pg from Group g inner join g.persons pg)

Maybe:

select p from Person p where not exists (select 1 from Group g where p member of g.persons)

is more efficient?

In any case... comments are welcome as to which is more efficient, but since both of these "work", marking the question as answered.

Upvotes: 1

Johanna
Johanna

Reputation: 5303

If you want to have all persons who are not in a given group g, it should be like 

from Person p where p.group.id != :gid;

and you set gid to the id of the given group.

If you want to have all persons who are in no group at all 

from Person p where p.group.id is null;

If you want to have all persons who are in no group at al, but it is possible the foreign key in Person has a group id which is not null but does not belong to an existing group (for example if the group was deleted without deleting the persons in it and without moving them to a different group)

from Person p where not exist (select 1 from Group g where g.id = p.group.id);

P. S. My statements are for HQL, but for JPQL it should be more or less the same.

Upvotes: 1

Related Questions