Using .annotate() and extra() together in django

Question

It seems i cannot use annotate and extra together when making a queryset This

discussions = game.gamediscussion_set.filter(reply_to=None).annotate(up_votes = Count('userUpVotes'), down_votes=Count('userDownVotes')).extra(select={'votes':"'userUpVotes' - 'userDownVotes'"}).order_by('votes')

returns

Caught Warning while rendering: Truncated incorrect DOUBLE value: 'userUpVotes'

I want to add both userUpVotes and userDownVotes together to get a 'votes' field, then order by this field.

userUpVotes is a related ManyToManyField of users (as is userDownVotes). So i need to count these first.

Any ideas?

Does it work if you take the single inverted commas out - eg `'votes': "userUpVotes - userDownVotes"` ? — Daniel Roseman, Jun 01 '10 at 18:48
Updated my question, also made an amend to it. Removing the inverted comma's didn't make a difference, However i have a new error which is raised from my new query. Which is "(1054, "Unknown column 'up_votes' in 'field list'")" — dotty, Jun 01 '10 at 19:05

score 3 · Accepted Answer · answered Jun 02 '10 at 16:32

This sort of thing is a lot easier if you store votes in the same table or column, with a value of +1 for an up vote and -1 for a down vote. You can still easily count the number of up or down votes with a simple filter, calculate the total number of votes with a simple count, and calculate the total score with the sum aggregate.

Sample code for storing votes in a separate table.

CHOICES = {
    1: 'UP',
    -1: 'DOWN'
}

class Vote(models.Model):
    user = models.ForiegnKey(User) # prevent ballot stuffing
    game = models.ForiegnKey(Game)
    vote = models.IntegerField(choices=CHOICES)

total_up_votes = Vote.objects.filter(game=GAME_INSTANCE).filter(vote=1).count()
total_votes = Vote.objects.filter(game=GAME_INSTANCE).count()
total_score = Vote.objects.filter(game=GAME_INSTANCE).aggregate(total=Sum('vote'))

total_score will be a dict: {'total': }

Using .annotate() and extra() together in django

1 Answers1