Django Querysets - Using .annotate() after .extra()

Question

Let us say I have a very simple model like this one:

class Test(models.Model):
    category = models.CharField(unique=True)
    start_time = models.DateTimeField()
    end_time = models.DateTimeField()

Basically, I want to use Django's ORM API to get the average duration per category. The SQL query will look something like this:

SELECT category, AVG(end_time - start_time) AS avg_duration
FROM test_table
GROUP BY category

I tried using the following code, but according to the docs, F() expressions in .annotate() is only available in Djano 1.8.

Test.objects.values('category', 'start_time', 'end_time').annotate(avg_duration=Avg(F(end_time) - F(start_time))

I also tried using .extra() like this, but I get a FieldError.

Test.objects.extra(select={'duration':'end_time - start_time'}).values('category', 'duration').annotate(avg_duration=Avg('duration'))

From the looks of things, the 2nd attempt suggests that the annotate function cannot resolve column aliases. Is this really the case, or am I missing something?

Furthermore, short of creating an additional column that stores derived information (duration per entry), using Django 1.8, and/or using raw SQL, what other alternatives can you guys recommend? Any help will very much be appreciated. Thanks!

Todor · Accepted Answer · 2015-02-27 13:34:05Z

2

Try this:

~~Test.objects.values('category').extra(select={'avg_duration':'AVG(end_time - start_time)'})~~

Yep, it won't happen, because

Any extra() call made after a values() call will have its extra selected fields ignored.

You can't use it before .values() either, because you will be forced to include it into the .values():

If you use a values() clause after an extra() call, any fields defined by a select argument in the extra() must be explicitly included in the values() call.

so this way any .extra select will be included in the grouping.

But you can't also make such kind of Avg without .extra so i think the only solution which had left is to use .raw

Test.objects.raw('\
    SELECT id, category, AVG(end_time - start_time) AS avg_duration \
    FROM test_table \
    GROUP BY category'
)

edited Feb 27, 2015 at 13:34

answered Feb 27, 2015 at 12:19

Todor

16.2k6 gold badges61 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wannabe Coder Over a year ago

I tried your suggestion and also tried this one fearing that the required columns for .extra() might not be detected. Test.objects.values('category', 'end_time', 'start_time').extra(select={'avg_duration':'AVG(end_time - start_time)'}) The results I got was similar to a basic select query like this: SELECT category, end_time, start_time FROM test_table Nice suggestion, but I think this is why it did not work: "Any extra() call made after a values() call will have its extra selected fields ignored." From the Django docs

Todor Over a year ago

Yep you are right. I played for a while with the shell testing different variations but cant find a solution different from .raw ;(

Wannabe Coder Over a year ago

I really appreciate the suggestions and the effort! I will be marking this as answered for the benefit of others using Django 1.7.

Bobort · Accepted Answer · 2016-08-24 21:33:11Z

0

If you are using Django 1.8 or higher, you could use something like this:

from django.db.models import Avg, F

Test.objects.annotate(avg_duration=Avg(F('end_time') - F('start_time')))

answered Aug 24, 2016 at 21:33

Bobort

3,23835 silver badges45 bronze badges

Collectives™ on Stack Overflow

Django Querysets - Using .annotate() after .extra()

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related