StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhibernate - How to do a Projection based on a subquery using Criteria API?
text
Body
copied!<p>I'm having a hard time trying to figure this out. The problem is, i have a table containing the score of users in a specific game. So, i have 2 entities called User and Game. Users have no direct relationship to a Game, but a i have a View in the database that has the following field:</p> <pre><code>public UserScore implements Serializable { @Id @ManyToOne private Game game; @Id @ManyToOne private User user; private Long score; } </code></pre> <p>So, what a i need is, given a User and a Game, i need the classification of this user in that game based o his score. The SQL query that do this is the following:</p> <pre><code>select user.name as name, scores.score as myScore, count(distinct countScores.score) as classification from user INNER JOIN userscore as scores ON user.id = scores.user_id, userscore as countScores where user.id = 66 and scores.game_id = 666 and countScores.game_id = 666 and countScores.score > scores.score group by user.id; </code></pre> <p>Assuming that 66 is the ID of the user you want the classification position and 666 the ID of the game that you are considering. So, this works, but how to translate it to the Hibernate criteria API? Since i can't create a Criteria with multiple root entities, i tryed to do the following:</p> <pre><code>DetachedCriteria countCriteria = DetachedCriteria.forClass(UserScore.class, "countScores") .add(Restrictions.eq("game.id", 666)) .add(Restrictions.gtProperty("score", "scores.score")) .setProjection(Projections.countDistinct("score")); this.session.createCriteria(User.class) .add(Restrictions.eq("id", 66)) .createAlias("userScore", "scores", JoinType.INNER_JOIN, Restrictions.eq("game", 666)) .setProjection(Projections.projectionsList() .add(Projections.property("name"), "name") .add(Projections.property("scores.score"), "myScore") .add(Projections.groupProperty("id")) // Here is where this is killing me: // .add(Projections.SOMETHING(countCriteria), "classification") ); ... </code></pre> <p>So, this is the problem, how i can select as a field in the return set the result of a subquery? There must be a way to do that, otherwise i have no ideas to solve this dilema.</p> <p>Thank you in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload