Create game Leaderboard using sqlProjection

08 / Nov / 2012 by Bhagwat Kumar 0 comments

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 🙂

FOUND THIS USEFUL? SHARE IT

Leave a Reply

Your email address will not be published. Required fields are marked *