Hibernate NOT IN subquery on junction table

Question

I'm trying to do a query in hibernate like 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 the Phone class:

@Entity
class Phone {

    @Id
    private Integer id;

    private String description;

}

Hibernate automatically creates a junction table called user_phone. Now i would like to select all the phones that aren't used by any user. I just cant figure out how to do that with Hibernate. I had 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 that returns all the users where the id is not the same as the id of any of the phones. So that's not what i'm looking for.

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"))
    ));