Extra Column in Group By When Using date_trunc

[anonymous]

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

[Tim Graham]

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.

[Tim Graham]

Closing as needs info in absence of a test to reproduce. Feel free to reopen if you can provide more details, thanks!

[Shai Berger]

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.