Django: using aggregates to show vote counts for basic polls app

Question

I'm making a very basic poll app. It's similar to the one in the Django tutorial but I chose to break out the vote counting aspect into its own model (the tutorial just adds a vote count field alongside each answer). Here's my models:

class PollQuestion(models.Model):
    question = models.CharField(max_length=75)

class PollAnswer(models.Model):
    poll = models.ForeignKey('PollQuestion')
    answer = models.CharField(max_length=75)

class PollVote(models.Model):
    poll = models.ForeignKey('PollQuestion')
    answer = models.ForeignKey('PollAnswer')
    date_voted = models.DateTimeField(auto_now_add=True)
    user_ip = models.CharField(max_length=75)

I'm trying to show all of the vote counts for a given poll. Here's my view code:

from django.db.models import Count
poll_votes = PollVote.objects.select_related('PollAnswer').filter(poll=poll_id).annotate(num_votes=Count('answer__id'))

When I output the results of this query I just get a single row per vote (eg I see about 40 'answers' for my poll, each one representing a vote for one of the 5 actual PollAnswers). If I look at the queries Django makes, it runs something like this for every vote in the poll:

SELECT `poll_answers`.`id`, `poll_answers`.`poll_id`, `poll_answers`.`answer` 
FROM `poll_answers` 
WHERE `poll_answers`.`id` = 101

Can anyone poke me in the right direction here? I get the feeling this should be easy.

EDIT: here's my template code, for completeness.

<ul>
    {% for vote in votes %}
        {{ vote.answer }} ({{ votes.num_votes }})<br />
    {% endfor %}
</ul>

boingboing · Answer 1 · 2012-06-12T21:44:21.933

1

Try:

 poll_votes = PollVote.objects.filter(poll=poll_id).annotate(num_votes=Count('answer__id'))

or:

poll_votes = PollVote.objects.values('poll', 'answer__answer').filter(poll=poll_id).annotate(num_votes=Count('answer__id'))

Relevant docs: Django offical docs

edited Jun 12 '12 at 21:44

answered Jun 11 '12 at 00:08

boingboing

161
2
10

Neither of these work: the first one has the same effect (just a row for each vote) and the second one doesn't return anything. – Matt Andrews Jun 11 '12 at 20:07
If it helps, I've added my template code to the question in case I'm doing something wrong there. – Matt Andrews Jun 11 '12 at 20:17
The second with values should return the correct value altough it is not a queryset but a value query set. – boingboing Jun 12 '12 at 21:42
In your template code you have {{ votes.num_votes }}, do you want all the votes for PollAnswer or PollQuestion? My second example ought to return the votes each PollAnswer. – boingboing Jun 12 '12 at 21:58

score 0 · Answer 2 · answered Jun 11 '12 at 20:36

0

Never mind, fixed it myself after finding a tutorial which uses the same sort of models as me.

Essentially the fix was in the view:

p = get_object_or_404(PollQuestion, pk=poll_id)
choices = p.pollanswer_set.all()

And in the template:

{% for choice in choices %}
    <p class="resultsList">{{choice.answer}} - {{choice.pollvote_set.count}}</p>
{% endfor %}

answered Jun 11 '12 at 20:36

Matt Andrews

2,868
3
31
53

Argh, this isn't quite the right fix though - it makes a query for each `PollAnswer` field (eg `SELECT * FROM poll_votes WHERE poll_votes.answer_id = 100`). Anyone got any ideas? – Matt Andrews Jun 11 '12 at 21:20

Django: using aggregates to show vote counts for basic polls app

2 Answers2