CODEWITHSUNDEEP
javasqlhibernate
Ozzie
Ozzie

Reputation: 11673

Hibernate NOT IN subquery on junction table

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?

Upvotes: 8

Views: 8506

Answers (2)

ygh
ygh

Reputation: 595

Since I reached here looking for how to form a subquery and not criteria, I wonder if other people might end up here the same way, too.

Since I figured out how to write the query in HQL, I wanted to share the 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, in a similar scenario. Hope it helps!

Upvotes: 4

Firo
Firo

Reputation: 30813

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

Upvotes: 6

Related Questions