Geek Answers Handbook

How to aggregate calculated field using django ORM? (without source SQL)

I am trying to find the cumulative duration of some events, the "start" and "end" fields are fields django.db.models.DateTimeField.

What I would like to do should be 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 cannot aggregate (Sum) start and end separately and then subtract in python because DB cannot create Sum DateTime objects.

A good way to do without raw sql?

+5
source share
3 answers

This answer has not yet satisfied me, my current job is working, but this is 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 request in seconds

Better SQL less solutions?

0

, Django 1.8, :

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

1.8, , Django 1.7.9 MySQL:

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

If you are in Postgres, you can use the django-pg-utils package and compute in the database. Include the duration field in seconds, and then take the amount

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

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

More articles:


All Articles