CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
christophe31
christophe31

Reputation: 6467

How to aggregate computed field with django ORM? (without raw SQL)

I'm trying to find the cumulated duration of some events, 'start' and 'end' field are both django.db.models.DateTimeField fields.

What I would like to do should have been written like this:

from django.db.models import F, Sum
from my.models import Event
Event.objects.aggregate(anything=Sum(F('start') - F('end')))
# this first example return: 
# AttributeError: 'ExpressionNode' object has no attribute 'split'

# Ok I'll try more SQLish:
Event.objects.extra(select={
                      'extra_field': 'start - end'
                    }).aggregate(Sum('extra_field'))
# this time:
# FieldError: Cannot resolve keyword 'extra_field' into field.

I can't agreggate (Sum) start and end separately then substract in python because DB can't Sum DateTime objects.

A good way to do without raw sql?

Upvotes: 6

Views: 1989

Answers (3)

arjunattam
arjunattam

Reputation: 2819

If you are on Postgres, then you can use the django-pg-utils package and compute in the database. Cast the duration field into seconds and then take the sum

from pg_utils import Seconds
from django.db.models import Sum

Event.objects.aggregate(anything=Sum(Seconds(F('start') - F('end'))))

Upvotes: 1

Ross Rogers
Ross Rogers

Reputation: 24218

Can't help Christophe without a Delorean, but I was hitting this error and was able to solve it in Django 1.8 like:

total_sum = Event.objects\
    .annotate(anything=Sum(F('start') - F('end')))\
    .aggregate(total_sum=Sum('anything'))['total_sum']

When I couldn't upgrade all my dependencies to 1.8, I found this to work with Django 1.7.9 on top of MySQL:

totals = self.object_list.extra(Event.objects.extra(select={
    'extra_field': 'sum(start - end)'
})[0]

Upvotes: 2

christophe31
christophe31

Reputation: 6467

This answer don't realy satisfy me yet, my current work around works but it's not DB computed...

reduce(lambda h, e: h + (e.end - e.start).total_seconds(), events, 0)

It returns the duration of all events in the queryset in seconds

Better SQL less solutions?

Upvotes: 0

Related Questions