Ordering objects by foreignkey field and not foreignkey count
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.
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')
you can use aggregation for this
r = Request.objects.filter(approved=True, vote__vote_type=True).annotate(total_vote=Count('vote'))
this will give you QuerySet of Request with approved=True . annotate part will give you extra attribut total_vote to every Request object with value count of all related Vote that have vote_type=True .
Do not forget that its QuerySet so to see how many Vote with vote_type=True for "first" Request you do r.first().total_vote