Max occurrences of a foreign key inside query

May 21, 2022

I’m trying to get an item with the most occurrences of a foreign key (votes), inside of a queryset (Questions, Choices).

I.E. I need to get the most popular vote to set the ‘winner’ attribute in the JsonResponse.

Any help on how I can figure this out?

Here is my view.

allchoices = [{
    'id':i.id,
    'question':i.question.id,
    'choice_text':i.choice_text,
    'votes':i.voter_set.all().count()
} for i in poll_choices]
return JsonResponse({
    "votes":choice.voter_set.all().count(),
    'winner':True,
    'success':True,
    'allchoices':allchoices
},safe=False,)

These are my models:

class Voter(models.Model):
    # Authentication of anonymous users
    choice = models.ForeignKey('PollChoice', on_delete=models.CASCADE)
    question = models.ForeignKey('PollQuestion', on_delete=models.CASCADE)
    

class PollChoice(models.Model):
    question = models.ForeignKey('PollQuestion', on_delete=models.CASCADE)
    choice_text = models.CharField(max_length=200)
    
    def __str__(self):
        return self.choice_text
    

class PollQuestion(models.Model):
    question = models.CharField(max_length=200)
    created = models.DateTimeField(auto_now_add=True)
    creator = models.ForeignKey('poll_creator',/\
         on_delete=models.CASCADE, null=False, blank=False)
    uuid = ShortUUIDField(length=8, max_length=12)
    
    def poll_choices(self):
        return self.pollchoice_set.all().annotate(voters=/\
            models.Count(models.F('voter'))).order_by('-voters')
    
    def choices(self):
        return self.pollchoice_set.all()

    def __str__(self):
        return f'{self.question}'

>Solution :

You can order by the number of related Voters, with:

from django.db.models import Count

poll_choice = PollChoice.objects.alias(
    num_voters=Count('voter')
).latest('num_voters')

this will retrieve the PollChoice with the largest amount of related Voters. You can filter the PollChoice further, for example to only consider PollChoices for a certain PollQuestion with:

from django.db.models import Count

poll_choice = PollChoice.objects.filter(question=my_question).alias(
    num_voters=Count('voter')
).latest('num_voters')