Django: cannot annotate using prefetch calculated attribute

user_5503827 0
Viewed 466

Target is to sum and annotate workingtimes for each employee on a given time range.

models:

class Employee(models.Model):
    first_name = models.CharField(max_length=64)

class WorkTime(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name="work_times")

    work_start = models.DateTimeField()
    work_end = models.DateTimeField()
    work_delta = models.IntegerField(default=0)

    def save(self, *args, **kwargs):
        self.work_delta = (self.work_end - self.work_start).seconds
        super().save(*args, **kwargs)

getting work times for each employee at a given date range:

queryset = Employee.objects.prefetch_related(
                Prefetch(
                    'work_times',
                    queryset=WorkTime.objects.filter(work_start__date__range=("2021-03-01", "2021-03-15"]))
                        .order_by("work_start"),
                    to_attr="filtered_work_times"
                )).all()

trying to annotate sum of work_delta to each employee:

queryset.annotate(work_sum=Sum("filtered_work_times__work_delta"))

This causes a FieldError:

Cannot resolve keyword 'filtered_work_times' into field. Choices are: first_name, id, work_times

How would one proceed from here? Using Django 3.1 btw.

relational-database orm django
2 Answers

You cannot use the prefetch_related values in the query because simply the prefetching is done separately, Django would first fetch the current objects and then make queries to fetch the related objects so the field you try to refer is not even part of the query you want to add it to.

Instead of doing this simply add a filter [Django docs] keyword argument to your aggregation function:

from django.db.models import  Q


start_date = datetime.date(2021, 3, 1)
end_date = datetime.date(2021, 3, 15)

result = queryset.annotate(work_sum=Sum("work_times__work_delta", filter=Q(work_times__work_start__date__range=(start_date, end_date))))
user_149918640

You should use filtering on annotations. I haven't tried, but I think the following code might help you:

from django.db.models import Sum, Q

Employee.objects.annotate(
    work_sum=Sum(
        'work_times__work_delta',
        filter=Q(work_times__work_start__date__range=["2021-03-01", "2021-03-15"])
    )
)
user_73959110
Related