#26658: "When Case" query generating rule in duplicate
-------------------------------------+-------------------------------------
Reporter: imaia | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.9
(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
-------------------------------------+-------------------------------------
Changes (by artemnesterenko):
* status: closed => new
* resolution: needsinfo =>
Comment:
I confirm that this behavior is still taking place in Django 2.2.
{{{#!python
class CustomQuerySet(QuerySet):
def annotate_statuses(self):
moment = now()
return self.annotate(
status=Case(
When(
timestamp__lte=moment
-
timedelta(seconds=settings.OFFLINE_SECONDS_THRESHOLD),
then=Value("OFFLINE"),
),
When(
idle__gte=settings.IDLE_SECONDS_THRESHOLD,
then=Value("IDLE"),
),
default=Value("ACTIVE"),
output_field=CharField(),
)
)
}}}
Having this queryset the query:
{{{#!python
Model.objects.annotate_statuses().values("status").annotate(count=Count("status"))
}}}
generates the following sql:
{{{#!sql
SELECT CASE
WHEN "my_table"."timestamp" <=
'2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
ELSE 'ACTIVE' END AS "status",
COUNT(CASE
WHEN "my_table"."timestamp" <=
'2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
ELSE 'ACTIVE' END) AS "count"
FROM "my_table"
GROUP BY CASE
WHEN "my_table"."timestamp" <=
'2019-04-19T09:23:04.681449+00:00'::timestamptz THEN 'OFFLINE'
WHEN "my_table"."idle" >= 300.0 THEN 'IDLE'
ELSE 'ACTIVE' END;
}}}
I expect it to generate something similar to:
{{{#!sql
select count(*), status
from (select CASE
WHEN (timestamp <=
'2019-04-19T09:23:04.681449+00:00'::timestamptz)
THEN 'OFFLINE'
WHEN (idle >= 300.0) THEN 'IDLE'
ELSE 'ACTIVE' END as status
from my_table
)
group by status;
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26658#comment:4>
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/063.f393ca1453da3c6f08ff47cce89cbffd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
