Hibernate NOT IN in a subquery on a join table

Question

I am trying to execute a query in sleep mode, for example, the following sql query:

SELECT phone.* FROM phone WHERE phone.id NOT IN (SELECT phone_id FROM user_phone)

I have the following entity classes:

 @Entity class User { @Id private Integer id; @ManyToMany private Set<Phone> phoneList; } 

and Phone class:

 @Entity class Phone { @Id private Integer id; private String description; } 

Hibernate automatically creates a connection table called user_phone. Now I would like to select all the phones that are not used by any user. I just can't figure out how to do this with Hibernate. I tried the following:

 Session session = (Session) entityManager.getDelegate(); Criteria criteria = session.createCriteria(Phone.class); DetachedCriteria subCriteria = DetachedCriteria.forClass(User.class); subCriteria.setProjection(Property.forName("phoneList")); criteria.add(Subqueries.propertyNotIn("id", subCriteria)) 

But this returns all users where the identifier does not match the identifier of any of the phones. So not what I'm looking for.

Does anyone know how to do this?

Answer 1

 Criteria criteria = session.createCriteria(Phone.class) .add(Subqueries.propertyNotIn("id", DetachedCriteria.forClass(User.class) .createAlias("phoneList", "phone") .setProjection(Property.forName("phone.id")) )); 

Answer 2

Since I got to the point of creating a subquery, not criteria, I wonder if other people can be here the same way.

Since I understood how to write a query in HQL, I would like to share a solution, just in case:

from phone p where p.id not in (select ph.id from User u join u.phoneList ph)

Worked for me according to a similar scenario. Hope this helps!