#24692: QuerySet.extra(select=...) is silently dropped with aggregations
-------------------------------+--------------------
Reporter: jdelic | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
Here is a minimal example:
{{{
#!python
from django.db import models
class Service(models.Model):
name = models.TextField()
class ServiceAction(models.Model):
performed = models.DateTimeField(auto_now_add=True)
service = models.ForeignKey(Service)
}}}
Then add some models:
{{{
#!python
>>> from dbtest.models import Service, ServiceAction
>>> from django.db.models import Min, Max
>>> s1 = Service.objects.create(name='test1')
>>> s2 = Service.objects.create(name='test2')
>>> ServiceAction.objects.create(service_id=1)
>>> ServiceAction.objects.create(service_id=1)
>>> ServiceAction.objects.create(service_id=1)
>>> ServiceAction.objects.create(service_id=2)
>>> ServiceAction.objects.create(service_id=2)
>>> ServiceAction.objects.create(service_id=2)
}}}
...and query them using aggregations:
{{{
#!python
>>> qs = ServiceAction.objects.extra(select={'period':
'(MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed"))'}).values('service_id').annotate(first_action=Min('performed'),
last_action=Max('performed'))
>>> print(qs.query)
SELECT "dbtest_serviceaction"."service_id",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
"dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
}}}
As you can see the {{{.extra(select=...)}}} has been dropped from the
query. I assume this happens on Django 1.6.x, 1.7.x and 1.8.x, but I have
only tested 1.6.11 and 1.8.
The expected outcome would have been:
{{{
SELECT "dbtest_serviceaction"."service_id",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action",
MAX("dbtest_serviceaction"."performed")-MIN("dbtest_serviceaction"."performed")
as "period" FROM "dbtest_serviceaction" GROUP BY
"dbtest_serviceaction"."service_id"
}}}
In Django 1.8 this exact query can be performed with the ORM itself
through the use of chained aggregations:
{{{
#!python
>>> qs =
ServiceAction.objects.values('service_id').annotate(first_action=Min('performed'),
last_action=Max('performed'), period=Max('performed')-Min('performed'))
>>> print(qs.query)
SELECT "dbtest_serviceaction"."service_id",
(MAX("dbtest_serviceaction"."performed") -
MIN("dbtest_serviceaction"."performed")) AS "period",
MAX("dbtest_serviceaction"."performed") AS "last_action",
MIN("dbtest_serviceaction"."performed") AS "first_action" FROM
"dbtest_serviceaction" GROUP BY "dbtest_serviceaction"."service_id"
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24692>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.a5ec0f4f073ba092c8296ee7c70341ac%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
