Is it possible to annotate a Django ORM query with a complex query

Question

I have three models, Contender, Week and Vote, each contender can have votes based on week,

class Contender(models.Model)
   week = models.ManyToManyField(Week)

class Week(models.Model):
   date_start = models.DateField()

class Vote(models.Model):
   contender = models.ForeignKey(Contender)
   week = models.ForeignKey(Week)

I would like to add something to the Contender, so I did this:

c = Count('vote', vote__week__date_start = "2011-01-03")
contenders = Contender.objects.all().annotate(vote_count=c).order_by('-vote_count')
contenders[0].vote_count

the problem is that when I add a vote with another Week (that has diferent date_start) the .vote_count value is changes and thus it seems like the extra parameters I pass to the Count object does not matter.

How do I do this type of annotation in the Django ORM?

Suor · Accepted Answer · 2011-05-14 11:41:35Z

You could start from Vote:

votes = Vote.objects.filter(week__date_start = "2011-01-03")      \
                    .values_list('contender')                     \
                    .annotate(cnt=Count('week')).order_by('-cnt')
contender_pks = [d[0] for d in votes]
contenders_dict = Contender.objects.in_bulk(contender_pks)

contenders = []
for pk, vote_count in votes:
    contender = contenders_dict[pk]
    contender.vote_count = vote_count
    contenders.append(conteder)

Also, you can do some denormalization - add

class VoteCount(models.Model):
   contender = models.ForeignKey(Contender)
   week = models.ForeignKey(Week)
   count = models.IntegerField(default=0)

and count votes in it (overriding Vote.save() or using post_save signal), then you will just do:

VoteCount.objects.filter(week__date_start = "2011-01-03") \
                 .select_related('contender')             \
                 .order_by('-count')

It will be much more efficient performancewise if you do such statistics often.

Collectives™ on Stack Overflow

Is it possible to annotate a Django ORM query with a complex query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related