How to group by and aggregate conditional in Django ORM

Question

I have a Django query to get average hours worked per day per employee (There could be multiple worklogs per day). I want to split these up in weekday and weekend work.

This is the worklog Model:

class Worklog(models.Model):
    worker = models.ForeignKey(Person, on_delete=models.CASCADE)
    day = models.DateField(null=False, blank=False)
    effort = models.FloatField()
    comment = models.TextField(null=True)
    project = models.ForeignKey(Project, on_delete=models.CASCADE)

I have tried the following:

    qs = Worklog.objects.filter(
                day__range=[start,end]
            ).values(
                'worker__fullname'

            ).annotate(
                weekday=Case(
                    When(Q(day__week_day=1) | Q(day__week_day=7), then=1),
                    default=0,
                    output_field=IntegerField(),
                )
            ).values(
                'worker__fullname'
            ).annotate(
                weekdayAvg=Case(
                    When(Q(weekday=0), then=Cast(
                    Sum('effort')/Count('day', distinct=True)/60/60, FloatField()
                )),
                    default=0,
                    output_field=FloatField(),
                ),
                weekendAvg=Case(
                    When(Q(weekday=1), then=Cast(
                    Sum('effort')/Count('day', distinct=True)/60/60, FloatField()
                )),
                    default=0,
                    output_field=FloatField(),
                )
            ).order_by('worker__fullname')

which gives me the result

     weekdayAvg  weekendAvg                        worker__fullname
0      9.125000        0.00                        Klaus
1      0.000000       11.00                        Klaus
2      6.977273        0.00                        Peter
3      7.827586        0.00                        Carl
4      0.000000       13.00                        Carl
5      8.169643        0.00                        Chris
6      0.000000        2.25                        Chris

However, the expected result would be more like:

     weekdayAvg  weekendAvg                        worker__fullname
0      9.125000        11.00                       Klaus
1      6.977273        0.00                        Peter
2      7.827586        13.00                       Carl
3      8.169643        2.25                        Chris

Any ideas how to achieve that?

Also I am happy about some simplification of my query. Thanks!

Removing the second `values( 'worker__fullname' )` gives me the exact same result — Matt, Apr 25 '19 at 14:18

Matt · Accepted Answer · 2019-04-26T10:19:55.300

I managed ot get it myself. The featur I did not know before is using the filter= parameter in aggregation functions (Sum, Count, etc.)

    qs = Worklog.objects.filter( 
            day__range=[start,end] 
        ).values( 
            'worker__fullname' 
        ).annotate( 
            weekdayAvg=Cast(Coalesce(Sum('effort', filter=(~Q(day__week_day=1) & ~Q(day__week_day=7)))/Count('day', distinct=True, filter=(~Q(day__week_day=1) & ~Q(day__week_day=7)))/60/60, 0), FloatField()),
            weekdayCnt=Cast(Coalesce(Count('day', distinct=True, filter=(~Q(day__week_day=1) & ~Q(day__week_day=7))), 0), FloatField()),
            weekendAvg=Cast(Coalesce(Sum('effort', filter=(Q(day__week_day=1) | Q(day__week_day=7)))/Count('day', distinct=True, filter=(Q(day__week_day=1) | Q(day__week_day=7)))/60/60, 0), FloatField()),
            weekendCnt=Cast(Coalesce(Count('day', distinct=True, filter=(Q(day__week_day=1) | Q(day__week_day=7))), 0), FloatField()),
        ).order_by('weekdayAvg', 'worker__fullname')

Also I added Coalesce(..., 0)as otherwiese the Sum would return None (as per this post)

How to group by and aggregate conditional in Django ORM

1 Answers1