[Django] #21182: Extra Column in Group By When Using date_trunc
Django
----------------------------------------------+--------------------
Reporter: anonymous | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.5
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I'm trying to basically sum the amount column of a bunch of financial
transactions per-month by category. I seem to have gotten 90% of the way
there with the ORM, but it looks like it is getting confused by my use of
date_trunc. In the Group By, all I want is category_id and month (the
date_trunc result), yet I also get the raw date column as well.
Here's the code I've put together for the ORM so far:
{{{
span="month"
truncate_date = django.db.connection.ops.date_trunc_sql(span, "date")
print Transaction.objects.extra({span: truncate_date}).values("category",
span).annotate(total=Sum("amount")).query
}}}
This results in:
{{{
SELECT (django_date_trunc('month', date)) AS "month",
"finance_transaction"."category_id", SUM("finance_transaction"."amount")
AS "total" FROM "finance_transaction" GROUP BY
"finance_transaction"."category_id", "finance_transaction"."date",
(django_date_trunc('month', date)) ORDER BY "finance_transaction"."date"
DESC
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/21182>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: anonymous | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timo):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
I'm skeptical of your use of `date_trunc_sql` -- this isn't a public API
that's designed for general use as far as I know.
Assuming there is a bug in Django though, it would be helpful if you could
include a test for Django's test suite so we can more easily reproduce the
issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/21182#comment:1>
Django
Reporter: anonymous | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.5
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timo):
* status: new => closed
* resolution: => needsinfo
Comment:
Closing as needs info in absence of a test to reproduce. Feel free to
reopen if you can provide more details, thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/21182#comment:2>
Django
Reporter: anonymous | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.5
(models, ORM) | Resolution: needsinfo
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by shai):
For anyone who looks at this later:
The query produced includes `ORDER BY "finance_transaction"."date" DESC`,
which hints that the user's Transaction model has a meta
`ordering=["-date"]`; if this is the case, the date field is
'''correctly''' added to the group-by, to allow this ordering. Ordering by
month instead should solve the problem.
--
Ticket URL: <https://code.djangoproject.com/ticket/21182#comment:3>