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 :
[sql]
select rank() over(order by score desc) as rnk, name, score from player
[/sql]

The good news is that Grails 2.2 supports sqlProjection where you can use native sql projections like this :
[groovy]
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]}"}
[/groovy]

If you are using older version of Grails then you can go with following alternatives:

• Using sessionFactory bean and createSQLQuery

[groovy]
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]}"}
[/groovy]

• Using hibernate criteria query

[groovy]
/* 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]}"}
[/groovy]

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 🙂