CODEWITHSUNDEEP
pythondjangodjango-queryset
tread
tread

Reputation: 11098

How to run a custom aggregation on a queryset?

I have a model called LeaveEntry:

class LeaveEntry(models.Model):
    date = models.DateField(auto_now=False, auto_now_add=False)
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.PROTECT,
        limit_choices_to={'is_active': True},
        unique_for_date='date'
    )
    half_day = models.BooleanField(default=False)

I get a set of LeaveEntries with the filter:

LeaveEntry.objects.filter(
    leave_request=self.unapproved_leave
).count()

I would like to get an aggregation called total days, so where a LeaveEntry has half_day=True then it is half a day so 0.5.

What I was thinking based on the django aggregations docs was annotating the days like this:

days = LeaveEntry.objects.annotate(days=<If this half_day is True: 0.5 else 1>)

Upvotes: 1

Views: 936

Answers (1)

j-i-l
j-i-l

Reputation: 10957

You can use django's conditional expressions Case and When (only for django 1.8+):

Keeping the order of filter() and annotate() in wind you can count the the number of days left for unapproved leaves like so:

from django.db.models import FloatField, Case, When
# ...
LeaveEntry.objects.filter(
    leave_request=self.unapproved_leave  # not sure what self relates to
    ).annotate(
        days=Count(Case(
            When(half_day=True, then=0.5),
            When(half_day=False, then=1),
            output_field=FloatField()
            )
        )
    )

Upvotes: 3

Related Questions