title: hibernate - How to do a Projection based on a subquery using Criteria API?
body: <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 &gt; 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>