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?
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': }