#28296: Add support for aggregation through subqueries
-------------------------------------+-------------------------------------
Reporter: László Károlyi | Owner: B
| Martsberger
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Matt Hegarty):
I wanted to comment in case it helps anyone else.
My use case was to use Django's subquery API to retrieve a count of
"active" days. That is, the number of days on which events were recorded.
The (postgres) SQL for this query would be:
{{{
SELECT "player_player"."id",
(SELECT count(1) from (select date_trunc('day', evt.timestamp)
"day"
FROM "player_event" evt
WHERE (evt."player_id" = "player_player"."id")
GROUP BY evt."player_id", "day") a) AS "day_count"
FROM "player_player"
}}}
I had to solve this using a subclass of Subquery:
{{{
class EventDayCountSubquery(Subquery):
template = "(SELECT COUNT(1) FROM (%(subquery)s) _count)"
output_field = IntegerField()
}}}
I could then use this as a regular annotation:
{{{
event_day_count = (
Event.objects.filter(player=OuterRef("pk"))
.annotate(day=TruncDay("timestamp")).values("day")
.annotate(count=Count("pk"))
)
Player.objects.annotate(
event_day_count_90_day=EventDayCountSubquery(event_day_count)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28296#comment:17>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.b2b1d971afc1fa3ca3e20f36a222f20d%40djangoproject.com.
