CODEWITHSUNDEEP
djangopostgresqldjango-modelsdjango-orm
utkbansal
utkbansal

Reputation: 2817

Ranking players using Django ORM (Postgres - RANK)

I have a Profile model that has a points field as follows:

class Profile(models.Model):
    user = models.OneToOneField(User)
    avatar = models.ImageField(default=None, null=True)
    experience = models.IntegerField(default=10)
    island = models.ForeignKey(Island, default=None, null=True)
    fcm_token = models.CharField(max_length=255, default=None, null=True, unique=True)
    points = models.DecimalField(default=0.0, max_digits=12, decimal_places=10)

What I want to do is that rank the users according to their points. I can do it using a simple raw SQL query which is as follows-

select id, RANK() over (order by points desc) as rank from player_profile ;

Which gives me the following output-

 id | rank 
----+------
  1 |    1
  2 |    2
  3 |    2
  4 |    2
(4 rows)

The initial data was-

id | avatar | experience | fcm_token | user_id | island_id |    points    
----+--------+------------+-----------+---------+-----------+--------------
  2 |        |         10 |           |       3 |         4 | 0.0000000000
  3 |        |         10 |           |       4 |         3 | 0.0000000000
  4 |        |         10 |           |       5 |         1 | 0.0000000000
  1 |        |         10 |           |       2 |         3 | 5.1010101010
(4 rows)

The problem is that I am unable to write an quivalent query using Django's ORM

Upvotes: 1

Views: 639

Answers (1)

Toshakins
Toshakins

Reputation: 147

Django <=1.10 doesn't allow to express window functions in pure Python.

However, it is possible to combine Python code and raw SQL to achieve needed result.

Using window functions for your use case is already explained on Stack Overflow here, please take a look: Clean way to use postgresql window functions in django ORM?

Upvotes: 1

Related Questions