Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate Criteria Subquery

Tags:

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.

like image 924
Gonzalo Avatar asked Sep 17 '10 19:09

Gonzalo


People also ask

How do you add subquery criteria?

Below is the pseudo-code for using sub-query using Criteria API. CriteriaBuilder criteriaBuilder = entityManager. getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder. createQuery(); Root<EMPLOYEE> from = criteriaQuery.

Does JPQL support subquery?

Unfortunately, JPQL supports it only in the WHERE clause and not in the SELECT or FROM clause. Subqueries can return one or multiple records and can use the aliases defined in the outer query.


2 Answers

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

like image 146
atrain Avatar answered Oct 23 '22 21:10

atrain


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.

like image 33
philip Avatar answered Oct 23 '22 19:10

philip