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

django, django-orm, django-queryset

Solution

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'
)

Problem

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!

Original source