Hibernate Criteria Subquery

Question

I need to do this SQL query with detachedCriteria:

SELECT g.id FROM games g
WHERE NOT EXISTS (
    SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id)

The idea is to get the ids from the games that aren't owned by the user. I tried like 10 different approaches with detachedCriteria but I get the "Unknown entity: null" MappingException The code should look like:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .add(Restrictions.eq("ug.user.id", 1))
   .add(Restrictions.eqProperty("ug.game.id","u.id"));
DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
   .add(Subqueries.notExists(subquery));

Setting also the projections to return only the id of the games.

Any ideas? I think Hibernate has some trouble joining the queries with no alias. Adding alias works but the results are quite wrong.

Are you not mapping an association between "games" and "user_games" in your objects / mappings? — matt b, Sep 17 '10 at 20:35

score 20 · Accepted Answer · answered Jul 06 '11 at 02:49

You need to add an alias, as follows:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .addAlias("ug.user", "user")
   .add(Restrictions.eq("user.id", 1))
   .addAlias("ug.game", "game")
   .add(Restrictions.eqProperty("game.id","u.id"));

That should help

score 13 · Answer 2 · answered Mar 06 '13 at 15:08

You need a projection and specifies which attribute that needs to be matched.

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
.add(Restrictions.eq("ug.user.id", 1))
.add(Restrictions.eqProperty("ug.game.id","u.id"))
.setProjection(Projections.property("ug.game.id"));

DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
.add(Property.forName("g.id").notIn(subquery));

I hope that helps.

score -8 · Answer 3 · edited Nov 12 '12 at 11:48

-8

Try

SELECT g.id FROM  users_games ug join ug.game g
WHERE NOT EXISTS (SELECT 1 FROM WHERE ug.user_id = 1)

edited Nov 12 '12 at 11:48

Janak Nirmal

22,706
18
63
99

answered Nov 12 '12 at 11:32

karen

1
1

7

Downvoting because OP's question was about how to do this in Hibernate, not in raw SQL. Also the "FROM WHERE" would be a syntax error in SQL. – Ed Griebel Nov 12 '14 at 16:52

Hibernate Criteria Subquery

3 Answers3

Linked