CODEWITHSUNDEEP
pythondjangoorm
Christopher
Christopher

Reputation: 25

Django ORM - Aggregate by Decorator

I am trying to use the Django ORM to aggregate by a field and SUM up the value returned by a decorator. I'm new to the Django ORM so forgive me if I'm missing something.

Here is my class:

class Payroll(models.Model):
    job = models.ForeignKey(Job, on_delete=models.CASCADE)      
    user = models.ForeignKey(User, on_delete=models.CASCADE)    
    ClockIn = models.DateTimeField()
    ClockOut = models.DateTimeField(null=True, blank=True)

    @property
    def time_worked(self):
        return self.ClockOut - self.ClockIn

I want to aggregate by user, and sum up the time_worked decorator. If I were writing the SQL myself it would be:

SELECT user, SUM(time_worked) 
FROM payroll
GROUP BY user

When I try

users_time_worked = Payroll.objects.aggregate(Sum('time_worked'))

the page crashes and the Django debug screen says: "Cannot resolve keyword 'time_worked' into field."

Is it possible to sum a timdelta field created by a decorator using the Django ORM? Is this something I should calculate within the view?

Upvotes: 1

Views: 215

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476554

Is it possible to sum a timdelta field created by a decorator using the Django ORM? Is this something I should calculate within the view?

No. A property does not exists at the database side. The database does not know anything about time_worked. This is something defined at the Django/Python layer. If you thus fetch .time_worked, Python will simply retrieve .ClockOut and .ClockIn, and return the subtraction of these two.

You however do not need time_worked here. You can subtract the ClockOut from the ClockIn at the database side:

from django.db.models import DurationField, F, Sum

users_time_worked = Payroll.objects.aggregate(
    total=Sum(F('clockOut') - F('clockIn'), output_field=DurationField())
)['total']

Upvotes: 1

Related Questions