#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 user0007):
Thank You for the answer.
Replying to [comment:9 jarshwah]:
> 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?
I've tried in that way. This QuerySet generates incorrect SQL (it groups
by `id` which is wrong and gives duplicate results).
SQL output:
{{{
SELECT (CAST(DATE_FORMAT(created_at, '%Y-%m-%d 00:00:00') AS DATETIME)) AS
`created_day`, `mymodel`.`product_id`, MAX(`mymodel`.`created_at`) AS
`max_date` FROM `mymodel` WHERE (`mymodel`.`user_id` = 1) GROUP BY
`mymodel`.`id` ORDER BY `max_date` DESC LIMIT 10
}}}
The query which works is:
{{{
SELECT (CAST(DATE_FORMAT(created_at, '%Y-%m-%d 00:00:00') AS DATETIME)),
`mymodel`.`product_id`, MAX(`mymodel`.`created_at`) AS `max_date` FROM
`mymodel` WHERE (`mymodel`.`user_id` = 1) GROUP BY `mymodel`.`product_id`,
(CAST(DATE_FORMAT(created_at, '%%Y-%%m-%%d 00:00:00') AS DATETIME)) ORDER
BY `max_date` DESC LIMIT 10
}}}
And the question is how to write it using QuerySet? :-) I was able to that
in Django 1.7.5 using `query.group by`, but now it throws errors.
--
Ticket URL: <https://code.djangoproject.com/ticket/24570#comment:10>
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.01eacb3a7653e663d5ef80fb3be4825a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
