CODEWITHSUNDEEP
pythondjangodjango-models
pythad
pythad

Reputation: 4277

Django annotate queryset on a specific value of relational model attribute

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?

Upvotes: 5

Views: 4730

Answers (2)

LanceS
LanceS

Reputation: 1

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

Upvotes: 0

user764357
user764357

Reputation:

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

Upvotes: 7

Related Questions