Order by dig-like rating in django

Question

I've got two models:

class Post(models.Model):
    #some fields
    pass

class Vote(models.Model):
    post = mdoels.ForeignKey(Post)
    value = models.IntegerField()

Votes' value can be either 1 or -1 (user can vote up or down).

How can i get a list of posts, ordered by their rating?

dr jimbob · Accepted Answer · 2011-06-06T19:23:18.577

You have to use annotation. https://docs.djangoproject.com/en/dev/topics/db/aggregation/

class Post(models.Model):         
    name = models.CharField(max_length=20)

class Vote(models.Model):
    post = models.ForeignKey(Post)
    value = models.IntegerField()
    type = models.CharField(max_length=2, choices=(("AW", "Awesomeness"), ("US", "Usefulness")))

Then you need to import Sum and can get a list of posts ordered by their vote_total like:

from django.db.models import Sum
Post.objects.annotate(vote_total=Sum('vote__value')).order_by('-vote_total')

EDIT:
Here's an example. Create several post objects and Vote Objects

Post.objects.get_or_create(id=1, name="post1")
Post.objects.get_or_create(id=2, name="post2")
Post.objects.get_or_create(id=3, name="post3")
Post.objects.get_or_create(id=4, name="post4")
Vote.objects.get_or_create(id=1, post_id=2, value=1, type="AW")
Vote.objects.get_or_create(id=2, post_id=2, value=1, type="AW")
Vote.objects.get_or_create(id=3, post_id=2, value=1, type="US")
Vote.objects.get_or_create(id=4, post_id=2, value=1, type="US")
Vote.objects.get_or_create(id=5, post_id=3, value=-1, type="AW")
Vote.objects.get_or_create(id=6, post_id=3, value=-1, type="AW")
Vote.objects.get_or_create(id=7, post_id=4, value=-1, type="AW")

Then posts = Post.objects.annotate(vote_total=Sum('vote__value')).order_by('-vote_total') will lead to [(post.name, post.vote_total) for post in posts] being.

[(u'post2', 4), (u'post4', -1), (u'post3', -2), (u'post1', None)]

This has an issue as things with no posts go at the very end. As django's Sum aggregation function takes the sum of no entries as None, not 0. This could be solved if you initially gave every post a vote with value 0 (or value 1 like reddit's system), you wouldn't get the None: e.g.:

for p in Post.objects.all():
    Vote.objects.get_or_create(post_id=p.id, value=0)

Then you'll get

>>> [(p.name, p.vote_total) for p in
     Post.objects.annotate(vote_total=Sum('vote__value')).order_by('-vote_total')]
[(u'post2', 4), (u'post1', 0), (u'post4', -1), (u'post3', -2)]

EDIT: Added type to the Vote model (done above), per comment about different vote types. You should be able to do something like (replace 'yourappname' with the name of your application to get the right db table):

select_dict = dict(vote_total = "SELECT SUM(value) FROM yourappname_vote WHERE yourappname_vote.post_id = yourappname_post.id",
                   awesome_total = "SELECT SUM(value) FROM yourappname_vote WHERE yourappname_vote.post_id = yourappname_post.id AND yourappname_vote.type = 'AW' ",
                   useful_total = "SELECT SUM(value) FROM yourappname_vote WHERE yourappname_vote.post_id = yourappname_post.id AND yourappname_vote.type = 'US' ",)

posts = Post.objects.all().extra(select = select_dict).order_by('-vote_total')

>>> [(p.name, p.vote_total, p.awesome_total, p.useful_total) for p in posts]
[(u'post2', 4, 2, 2),
 (u'post1', 0, 0, 0),
 (u'post4', -1, -1, 0),
 (u'post3', -2, -2, 0)]

Now each post now will have a vote_total, as well awesome_total and useful_total in one query to the database. A bit uglier than the ORM, but still quite readable (and this is how the Sum aggregation works. ). You still will have to give each category of votes an initial vote to get past the None appearing out of order.

Wow, thanks that's a great solution - I should be ashamed, i didn't know that the Sum aggregation function existed. Btw, is there anyway to filter summed votes? I mean, in the actual project i have different types of votes (for example, "awesomness" and "usefulness"), can i somehow filter the related votes that are being summed to order only by a certain vote type? — DataGreed, Jun 06 '11 at 18:49
@DataGreed: Ok, here's an example working with filtering only one kind of vote type using SQL SELECT statements in an extra clause. — dr jimbob, Jun 06 '11 at 19:21
thank you very much, you are my savior :) I guess, i should learn SQL, 'cause ORM is not enough in some cases. — DataGreed, Jun 06 '11 at 21:17
@DataGreed: glad the help was appreciated. This is the very rare case in django where it helps to know a little SQL to go beyond the ORM (and the SQL is fairly simple and readable). Its also well documented in the django docs, see the second example (only differences is they used COUNT instead of SUM as the aggregation and didn't have an AND in the where clause): https://docs.djangoproject.com/en/1.2/ref/models/querysets/#extra — dr jimbob, Jun 07 '11 at 14:22
FYI, there is an open [Django ticket](https://code.djangoproject.com/ticket/10929) about default values for `Sum()`. — Gary, Feb 07 '12 at 22:49

psoares · Answer 2 · 2011-06-06T17:46:26.250

0

I think this should work:

Vote.objects.values_list('post', flat=True).order_by('value')

To invert the order:

Vote.objects.values_list('post', flat=True).order_by('-value')

UPDATE

Since post is a ForeignKey to Post, let's assume your model is something like this:

class Post(models.Model):
    post_id = models.AutoField(primary_key=True)
    post_name = models.CharField(max_length=30, unique=True)

So this should work, it would return the names:

 Vote.objects.values_list('post__post_name', flat=True).order_by('value')

note_

If you want to make queries with the list and some error appear, just transform it to a list:

list(Vote.objects.values_list('post__post_name', flat=True).order_by('value')))

edited Jun 06 '11 at 17:46

answered Jun 06 '11 at 17:17

psoares

4,733
7
41
55

Hmm, that returns somthing strange like 3, 1, 1, 1, 1, 3, 3, 1, 3, 3, 1, 3, 3, 3, 3, 3, 3, 3, 1, 3, '...(remaining elements truncated)...'] – DataGreed Jun 06 '11 at 17:40
I think you misunderstood the problem. You are just ordering the list of votes and saying which vote the name goes to. The system DataGreed wanted was to list posts in order of the sum of their votes (as my answer showed). – dr jimbob Jun 06 '11 at 18:41
obsviouly I didn't understood that. but if you have already provided a answer good :) and a good answer, very well explained! – psoares Jun 06 '11 at 18:47

Order by dig-like rating in django

2 Answers2