#26602: RawSQL window functions break count()
-------------------------------------+-------------------------------------
Reporter: daggaz | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Brobin):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> I wanted to annotate my objects with a running total:
>
> {{{
> class A(models.Model):
> amount = models.IntegerField()
> created = models.DateTimeField(auto_now_add=True)
>
> qs = A.objects.annotate(total=RawSQL("SUM(amount) OVER (ORDER BY
> created)", []))
> }}}
>
> That works fine, and I get a running total for each object, but I cannot
> call `count()` on that queryset:
> {{{
> >>> qs.count()
> Traceback...
> ProgrammingError: window functions not allowed in GROUP BY clause
> }}}
>
> Using `extra()`, I can get the same annotation behaviour as well being
> able to call `count()`:
> {{{
> >>> qs = A.objects.extra(select={'total': 'SUM(amount) OVER (ORDER BY
> created)'})
> >>> qs.count()
> 8
> }}}
New description:
I wanted to annotate my objects with a running total:
{{{#!python
class A(models.Model):
amount = models.IntegerField()
created = models.DateTimeField(auto_now_add=True)
qs = A.objects.annotate(total=RawSQL("SUM(amount) OVER (ORDER BY
created)", []))
}}}
That works fine, and I get a running total for each object, but I cannot
call `count()` on that queryset:
{{{
>>> qs.count()
Traceback...
ProgrammingError: window functions not allowed in GROUP BY clause
}}}
Using `extra()`, I can get the same annotation behaviour as well being
able to call `count()`:
{{{
>>> qs = A.objects.extra(select={'total': 'SUM(amount) OVER (ORDER BY
created)'})
>>> qs.count()
8
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26602#comment:1>
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/064.4297d6403901901db12d86de8af1ca20%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
