Create game Leaderboard using sqlProjection
Recently I was working on a Facebook game application using Grails 2.0 and Postgres database. The use case was to show the game leaderboard with ranks of the players. Players with same score should be given same rank and next player rank should be incremented rank of last player. Here is the sample data for players and expected result:
There is a rank() function in Postgres which solved my problem. Here is the sample query :
select rank() over(order by score desc) as rnk, name, score from player
The good news is that Grails 2.2 supports sqlProjection
where you can use native sql projections
like this :
Player.createCriteria().list{ projections { sqlProjection ('rank() over(order by score desc) as rnk', ['rnk'], [org.hibernate.Hibernate.INTEGER]) property('name') property('score') } } .each { println "${it[0]} \t ${it[1]} \t ${it[2]}"}
If you are using older version of Grails then you can go with following alternatives:
- Using sessionFactory bean and createSQLQuery
String query="select rank() over(order by score desc) as rnk, name, score from player" //inject sessionFactory bean object (def sessionFactory) sessionFactory.currentSession.createSQLQuery(query).list() .each { println "${it[0]} \t ${it[1]} \t ${it[2]}"}
/* import org.hibernate.Hibernate import org.hibernate.type.Type import org.hibernate.criterion.Projections import org.hibernate.criterion.Restrictions */ sessionFactory.currentSession.createCriteria(Player) .setProjection(Projections.projectionList() .add(Projections.sqlProjection( "rank() over(order by score desc) as rnk", ["rnk"] as String[], [Hibernate.INTEGER] as Type[])) .add(Projections.property("name")) .add(Projections.property("score"))) .list() .each {println "${it[0]} \t ${it[1]} \t ${it[2]}"}
I have not been able to find a solution to find rank of specific user without loading all the records/rows and doing a groovy find on the result. The rank function assigns rank to each row based on the final result set returned by the query. So if you add a restriction to select only specific player’s rank, it will be always 1 (as the final result set will have only one record).
Hope you will share a better way to find rank of specific user