Django annotate queryset on a specific value of relational model attribute

Question

Suppose there is a structure like this:

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 to annotate each object of Model1 with count of Model2 objects where status equals a specific value (status number is this particular example).

In my pseudo code it would look like:

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 can't annotate the queryset in this way as Django doesn't resolve status=<n>.

What is the right way to achieve what I want?

pythad · Accepted Answer · 2016-01-14 17:16:31Z

7

If you are using Django 1.8 or above you can use Conditional Aggregations, these should work for annotate querysets.

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())
    )
)

edited Jan 14, 2016 at 17:16

pythad

4,2972 gold badges23 silver badges41 bronze badges

answered Jan 13, 2016 at 21:45

user764357

Sign up to request clarification or add additional context in comments.

8 Comments

pythad Over a year ago

Thanks, this looks realy good, but I need to annotate objects and not to aggregate. Do you have any ideas on annotatios like these?

user764357 Over a year ago

I've modified the answer, it should still work the same way. Give it a shot and let me know how it goes!

Pynchia Over a year ago

@pythad here you can have a look for yourself at how to perform a query on related models. e.g. Reporter.objects.filter(article__pk=1) with your models would be Model1.objects.filter(model2__status=1). Therefore, if you are using _set it means there is some other code you have forgotten to mention (i.e. the query is performed on a specific record/instance of 'model1`. Maybe iterating?)

pythad Over a year ago

@Pynchia, I simplified my question a bit. There is a related_name in my project, i query on it. Just messed up related name with _set. I am sorry, thanks for your pointing.

Pynchia Over a year ago

OK, then to make the question helpful for others, please do edit you models in the question and add the related_name parameter to field in Model2. Your question is very relevant and there aren't many useful answers (recent ones). So the answer you got will be quite useful indeed to the Django community

|

LanceS · Accepted Answer · 2021-04-30 20:44:30Z

You can use an Exists Subquery:

from django.db.models.expressions import Exists, ExpressionWrapper, OuterRef, Subquery, Value
from django.db.models.fields import BooleanField

queryset = Model1.objects.all()
queryset.annotate(
  declined=ExpressionWrapper(
    Exists(Model2.objects.filter(                        
      field=OuterRef('id'),
      status=0)),
    output_field=BooleanField()))),
  not_sure=ExpressionWrapper(
    Exists(Model2.objects.filter(
      field=OuterRef('id'),
      status=1)),
    output_field=BooleanField()))),
  accepted=ExpressionWrapper(
    Exists(Model2.objects.filter(                        
      field=OuterRef('id'),
      status=2)),
    output_field=BooleanField())))
)

To make it a bit more clear/reusable you can refactor into a function:

def is_status(status_code):
   return ExpressionWrapper(
     Exists(Model2.objects.filter(                        
        field=OuterRef('id'),
        status=status_code)),
     output_field=BooleanField())))

Model1.objects.annotate(
  declined=is_status(0),
  not_sure=is_status(1),
  accepted=is_status(2)
)

Collectives™ on Stack Overflow

Django annotate queryset on a specific value of relational model attribute

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related