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:
\n<\/p>\n
There is a rank() function in Postgres which solved my problem. Here is the sample query :
\n[sql]
\nselect rank() over(order by score desc) as rnk, name, score from player
\n[\/sql]<\/p>\n
The good news is that Grails 2.2 supports If you are using older version of Grails then you can go with following alternatives:<\/p>\n [groovy] [groovy] sessionFactory.currentSession.createCriteria(Player) 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).<\/p>\n Hope you will share a better way to find rank of specific user \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":9,"footnotes":""},"categories":[7],"tags":[606,1116,1114,1113,1115],"class_list":["post-9216","post","type-post","status-publish","format-standard","hentry","category-grails","tag-grails-createcriteria","tag-leaderboard","tag-postgres","tag-rank","tag-sqlprojection"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\tsqlProjection<\/code> where you can use native sql projections<\/code> like this :
\n[groovy]
\nPlayer.createCriteria().list{
\n projections {
\n sqlProjection (‘rank() over(order by score desc) as rnk’,
\n [‘rnk’], [org.hibernate.Hibernate.INTEGER])
\n property(‘name’)
\n property(‘score’)
\n }
\n }
\n.each { println &quot;${it[0]} \\t ${it[1]} \\t ${it[2]}&quot;}
\n[\/groovy]<\/p>\n\n
\nString query=&quot;select rank() over(order by score desc) as rnk, name, score from player&quot;
\n\/\/inject sessionFactory bean object (def sessionFactory)
\nsessionFactory.currentSession.createSQLQuery(query).list()
\n.each { println &quot;${it[0]} \\t ${it[1]} \\t ${it[2]}&quot;}
\n[\/groovy]<\/p>\n
\n\/* import org.hibernate.Hibernate
\nimport org.hibernate.type.Type
\nimport org.hibernate.criterion.Projections
\nimport org.hibernate.criterion.Restrictions *\/<\/p>\n
\n .setProjection(Projections.projectionList()
\n .add(Projections.sqlProjection(
\n &quot;rank() over(order by score desc) as rnk&quot;,
\n [&quot;rnk&quot;] as String[],
\n [Hibernate.INTEGER] as Type[]))
\n .add(Projections.property(&quot;name&quot;))
\n .add(Projections.property(&quot;score&quot;)))
\n .list()
\n .each {println &quot;${it[0]} \\t ${it[1]} \\t ${it[2]}&quot;}
\n[\/groovy]<\/p>\n<\/ul>\n