#24570: `group_by` clause does not resolve keywords defined in `extra` clause.
-------------------------------------+-------------------------------------
Reporter: user0007 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by jarshwah):
What you were doing with your original queryset seems suspect to me, it
looks like there are better ways to accomplish your goals in a completely
supported way. Let me highlight some issues with your original queryset
(I'll remove pieces that aren't relevant for brevity):
{{{
MyModel.objects.annotate(
max_date=models.Max('created_at')
).extra(
{'created_at': truncate_date} # here you're overwriting the field
called created_at, you should choose a new alias
).values(
'product_id', 'created_at', 'max_date'
)
qs.query.group_by = ['product_id', truncate_date] # this is private
API, but you'd need to use the alias
# name of the extra clause ("created_at"), which is ambiguous because
# you've used the same name. group_by=['product_id', 'created_extra']
qs.order_by('-max_date')[:10]
}}}
I've written a comparable (but not exact) queryset with 1.8 that I'd like
you to take a look at.
{{{
In [46]: truncate_date = connection.ops.date_trunc_sql('day', 'created')
In [47]: qs = Stats.objects.extra({'created_day':
truncate_date}).values('created_day').annotate(max_growth=Max('growth'))
In [48]: print(qs.query)
SELECT (DATE_TRUNC('day', created)) AS "created_day",
MAX("scratch_stats"."growth") AS "max_growth" FROM "scratch_stats" GROUP
BY (DATE_TRUNC('day', created))
In [49]: for q in qs:
....: print(q)
....:
{'created_day': datetime.datetime(2015, 3, 29, 0, 0, tzinfo=<UTC>),
'max_growth': 16}
{'created_day': datetime.datetime(2015, 3, 24, 0, 0, tzinfo=<UTC>),
'max_growth': 11}
{'created_day': datetime.datetime(2015, 3, 31, 0, 0, tzinfo=<UTC>),
'max_growth': 17}
{'created_day': datetime.datetime(2015, 3, 25, 0, 0, tzinfo=<UTC>),
'max_growth': 18}
{'created_day': datetime.datetime(2015, 3, 23, 0, 0, tzinfo=<UTC>),
'max_growth': 17}
...
}}}
Note that the query groups by the extra created_day clause because we've
not shadowed any model fields, and we've named it in our values call. The
above code I've written **is** fully supported too, although I'd encourage
you to avoid using `extra` and to instead use
[https://docs.djangoproject.com/en/dev/ref/models/expressions/ Query
Expressions]. Django should provide proper db_functions for extracting
date parts, but that's a work in progress (I would imagine Django 1.9 will
have something).
What I think your query should look like:
{{{
truncate_date = connection.ops.date_trunc_sql('day', 'created_at')
qs = MyModel.objects.filter(
user_id=1
).annotate(
max_date=models.Max('created_at')
).extra(
{'created_day': truncate_date}
).values(
'product_id', 'created_day', 'max_date'
).order_by('-max_date')[:10]
}}}
Can you give that a go and let us know the outcome please?
--
Ticket URL: <https://code.djangoproject.com/ticket/24570#comment:9>
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/066.149fe672ebefd925d50a807f7d30f8f7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
