Ordering objects by foreignkey field and not foreignkey count

Question

I am making a request and a voting feature for the request:

class Request(models.Model):
    title = models.CharField(max_length=255)
    approved = models.BooleanField(default=False)
    user = models.OneToOneField(User, on_delete=models.CASCADE)

class Vote(models.Model):
    request = models.ForeignKey(Request, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    vote_type = models.BooleanField()

I would like to order requests by the total vote counts in descending order by substracting different vote_type's (True/False) for each request. Meaning order requests with highest number of votes to lowest number of votes.

I can order by the vote this way:

Request.objects.order_by('-vote')

But this just gives me which request has highest number of foreign key count, and not the actual vote count.

I can get the actual vote counts for a request like this:

def get_vote_count(obj):
    return obj.vote_set.filter(vote_type=True).count() - obj.vote_set.filter(vote_type=False).count()

But I can't figure out how to achieve this when getting all the requests and ordering them in the view.

Answer 1

I think you can achieve it by using the conditional expressions.

Try this:

from django.db import models

Request.objects.annotate(
    num_of_true_vote_type=models.Count(
        models.Case(When(vote__vote_type=True, then=1), output_field=models.IntegerField())
    ),
    num_of_false_vote_type=models.Count(
        models.Case(When(vote__vote_type=False, then=1), output_field=models.IntegerField())
    ),
    difference=models.F('num_of_true_vote_type') - models.F('num_of_false_vote_type')
).order_by('-difference')