Django annotates a request for a specific attribute value of a relational model

Question

Suppose that there is such a structure:

PARTICIPATION_STATUSES = ( (0, 'No, thanks'), (1, 'I may attend'), (2, 'I\'ll be there'), ) class Model1(models.Model): # ... class Model2(models.Model): status = models.PositiveIntegerField( _('participation status'), choices=PARTICIPATION_STATUSES) field = models.ForeignKey(Model1, related_name='model1_participation') 

What I want to do is annotate each Model1 object with the number of Model2 objects where the status is equal to a certain value (the status number is a concrete example).

In my pseudo code, it would look like this:

 queryset = Model1.objects.all() queryset.annotate(declined=Count('model1_participation__status=0')) queryset.annotate(not_sure=Count('model1_participation__status=1')) queryset.annotate(accepted=Count('model1_participation__status=2')) 

But I cannot annotate the request this way, since Django does not allow status=<n> .

What is the right way to achieve what I want?

Answer 1

If you use Django 1.8 or higher, you can use Conditional aggregations , they should work for annotate queries.

 from django.db.models import IntegerField, Case, When, Count queryset = Model1.objects.all() queryset = queryset.annotate( declined=Count( Case(When(model1_participation__status=0, then=1), output_field=IntegerField()) ), not_sure=Count( Case(When(model1_participation__status=1, then=1), output_field=IntegerField()) ), accepted=Count( Case(When(model1_participation__status=2, then=1), output_field=IntegerField()) ) )