CODEWITHSUNDEEP
djangodjango-modelsdjango-templatesdjango-viewsdjango-queryset
Frederik Vanclooster
Frederik Vanclooster

Reputation: 513

Django Query: How to order posts by amount of upvotes?

I'm currently working on a website (with Django), where people can write a story, which can be upvoted by themselves or by other people. Here are the classes for Profile, Story and Upvote:

class Profile(AbstractBaseUser, PermissionsMixin):
  email = models.EmailField(unique=True)
  first_name = models.CharField(max_length=30, null=True)
  last_name = models.CharField(max_length=30, null=True)


class Story(models.Model):
  author = models.ForeignKey('accounts.Profile', on_delete=models.CASCADE, related_name="author")
  title = models.CharField(max_length=50)
  content = models.TextField(max_length=10000)


class Upvote(models.Model):
  profile = models.ForeignKey('accounts.Profile', on_delete=models.CASCADE, related_name="upvoter")
  story = models.ForeignKey('Story', on_delete=models.CASCADE, related_name="upvoted_story")
  upvote_time = models.DateTimeField(auto_now=True)

As you can see, Upvote uses two foreign keys to store the upvoter and the related story. Now I want to make a query which gives me all the stories, sorted by the amount of upvotes they have. I've tried my best to come up with some queries myself, but it's not exactly what I'm searching for.

This one doesn't work at all, since it just gives me all the stories in the order they were created, for some reason. Also it contains duplicates, although I want them to be grouped by story.

hot_feed = Upvote.objects.annotate(upvote_count=Count('story')).order_by('-upvote_count')

This one kind of works. But if I'm trying to access a partical story in my template, it just gives me back the id. So I'm not able to fetch the title, author and content from that id, since it's just an integer, and not an object.

hot_feed = Upvote.objects.values('story').annotate(upvote_count=Count('story')).order_by('-upvote_count')

Could someone help me out with finding the query I'm searching for?

Upvotes: 2

Views: 151

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476813

You are querying from the wrong model, you here basically fetch Upvotes ordered by the number of stories, or something similar.

But your probaby want to retrieve Storys by the number of upvotes, so you need to use Story as "queryset root", and annotate it with the number of upvotes:

Story.objects.annotate(
    upvote_count=Count('upvoted_story')
).order_by('-upvote_count')

I think the related_name of your story is however a bit "misleading". The related_name is the name of the relation "in reverse", so probably a better name is upvotes:

class Upvote(models.Model):
    profile = models.ForeignKey(
        'accounts.Profile',
        on_delete=models.CASCADE,
        related_name='upvotes'
    )
    story = models.ForeignKey(
        'Story',
        on_delete=models.CASCADE,
        related_name='upvotes'
    )
    upvote_time = models.DateTimeField(auto_now=True)

In that case the query is:

Story.objects.annotate(
    upvote_count=Count('upvotes')
).order_by('-upvote_count')

Upvotes: 3

Related Questions