Thanks Chris for your response; however, its not the solution I'm looking for. I need the ranking to go in sequential order - the solution you provided will just make my points, or scores in your example, the rank. I need, for example, the person with the highest number of scores/points with the rank 1 associated their record in the queryset. Then rank 2 for the next person with highest score/points. etc
Any ideas on how to achieve that? Only solution I'm seeing at the moment is to add rank to the model and update rank every time a user calls the leaderboard - which would be expensive and slow wouldn't it? On Wednesday, June 20, 2012 8:25:06 AM UTC-7, Chris Lawlor wrote: > > If you can use > .annotate()<https://docs.djangoproject.com/en/dev/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset>to > calculate the rank, you can further filter / order the annotation > results. > > An example: > > class Book(models.Model): > title = models.CharField(max_length=120) > ... > > class Vote(models.Model): > user = models.ForeignKey('User') > score = models.IntegerField(choices=((-1, 'Thumbs Down'), (+1, 'Thumbs > Up'))) > book = models.ForeignKey('Book', related_name='votes') > > > To rank the Books by the sum of thumbs up and thumbs down votes, you could > do the following: > > Book.objects.all().annotate(rank=Sum('votes__score')) > > Each object returned by this QuerySet will now have a 'rank' attribute, > which is the sum of the Vote scores. You can then filter or order by rank. > For example, to retrieve only books with a positive overall rank: > > > Book.objects.all().annotate(rank=Sum('votes__score')).filter(rank__gt=0).order_by('rank') > > Hope that helps. > > * Code isn't tested > > On Tuesday, 19 June 2012 13:27:21 UTC-4, Peter Ung wrote: >> >> Hi All, >> >> I'm trying to do ranking of a QuerySet efficiently (keeping it a QuerySet >> so I can keep the filter and order_by functions), but cannot seem to find >> any other way then to iterate through the QuerySet and tack on a rank. I >> dont want to add rank to my model. >> >> I know how I can get the values I need through SQL query, but can't seem >> to translate that into Django: >> >> SET @rank = 0, @prev_val = NULL; >>> SELECT rank, name, school, points FROM >>> (SELECT @rank := IF(@prev_val = points, @rank, @rank+1) AS rank, >>> @prev_val := points, points, CONCAT(users.first_name, ' ', users.last_name) >>> as name, school.name as school >>> FROM accounts_collegebrainuserprofile >>> JOIN schools_school school ON school_id = school.id >>> JOIN auth_user users ON user_id = users.id >>> ORDER BY points DESC) as profile >>> ORDER BY rank DESC >>> >> >> I found that if I did iterate through the QuerySet and tacked on 'rank' >> manually and then further filtered the results, my 'rank' would disappear. >> Is there any other way you can think of to add rank to my QuerySet? Is >> there any way I could do the above query and get a QuerySet with filter and >> order_by functions still intact? I'm currently using the jQuery DataTables >> with Django to generate a leaderboard with pagination (which is why I need >> to preserver filtering and order_by). >> >> Thanks in advance! Sorry if I did not post my question correctly - any >> help would be much appreciated. >> >> Peter >> >> > On Wednesday, June 20, 2012 8:25:06 AM UTC-7, Chris Lawlor wrote: > > If you can use > .annotate()<https://docs.djangoproject.com/en/dev/topics/db/aggregation/#generating-aggregates-for-each-item-in-a-queryset>to > calculate the rank, you can further filter / order the annotation > results. > > An example: > > class Book(models.Model): > title = models.CharField(max_length=120) > ... > > class Vote(models.Model): > user = models.ForeignKey('User') > score = models.IntegerField(choices=((-1, 'Thumbs Down'), (+1, 'Thumbs > Up'))) > book = models.ForeignKey('Book', related_name='votes') > > > To rank the Books by the sum of thumbs up and thumbs down votes, you could > do the following: > > Book.objects.all().annotate(rank=Sum('votes__score')) > > Each object returned by this QuerySet will now have a 'rank' attribute, > which is the sum of the Vote scores. You can then filter or order by rank. > For example, to retrieve only books with a positive overall rank: > > > Book.objects.all().annotate(rank=Sum('votes__score')).filter(rank__gt=0).order_by('rank') > > Hope that helps. > > * Code isn't tested > > On Tuesday, 19 June 2012 13:27:21 UTC-4, Peter Ung wrote: >> >> Hi All, >> >> I'm trying to do ranking of a QuerySet efficiently (keeping it a QuerySet >> so I can keep the filter and order_by functions), but cannot seem to find >> any other way then to iterate through the QuerySet and tack on a rank. I >> dont want to add rank to my model. >> >> I know how I can get the values I need through SQL query, but can't seem >> to translate that into Django: >> >> SET @rank = 0, @prev_val = NULL; >>> SELECT rank, name, school, points FROM >>> (SELECT @rank := IF(@prev_val = points, @rank, @rank+1) AS rank, >>> @prev_val := points, points, CONCAT(users.first_name, ' ', users.last_name) >>> as name, school.name as school >>> FROM accounts_collegebrainuserprofile >>> JOIN schools_school school ON school_id = school.id >>> JOIN auth_user users ON user_id = users.id >>> ORDER BY points DESC) as profile >>> ORDER BY rank DESC >>> >> >> I found that if I did iterate through the QuerySet and tacked on 'rank' >> manually and then further filtered the results, my 'rank' would disappear. >> Is there any other way you can think of to add rank to my QuerySet? Is >> there any way I could do the above query and get a QuerySet with filter and >> order_by functions still intact? I'm currently using the jQuery DataTables >> with Django to generate a leaderboard with pagination (which is why I need >> to preserver filtering and order_by). >> >> Thanks in advance! Sorry if I did not post my question correctly - any >> help would be much appreciated. >> >> Peter >> >> > -- You received this message because you are subscribed to the Google Groups "Django users" group. To view this discussion on the web visit https://groups.google.com/d/msg/django-users/-/zJMHkAa7ycIJ. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
