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.
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.
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.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With