[Django] #34206: annotation of another annotation duplicate query rule
Django
------------------------------------------------+--------------------------
Reporter: Mohammad99k | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Uncategorized | Version: 4.1
Severity: Normal | Keywords: annotate
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------------+--------------------------
When I use an annotation in another annotation, query is duplicated like
bellow:
{{{
groups =
StudyGroup.objects.filter(group_filter).select_related('parent').annotate(
no_members=Count('student', distinct=True),
point=Sum('student__point__point', filter=point_filter),
avg_point=ExpressionWrapper(F('point') / F('no_members'),
output_field=FloatField()))
}}}
In above queryset, {{{Count('student', distinct=True)}}} calculate for
no_members and when I use no_members in avg_point annotation again query
calculate {{{Count('student', distinct=True)}}} instead of use no_members
directly.
{{{
COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
SUM(`study_league_point`.`point`) AS `point`,
(
SUM(`study_league_point`.`point`) / COUNT(DISTINCT
`users_student`.`user_id`)
) AS `avg_point`,
}}}
In above my SQL code {{{COUNT(DISTINCT `users_student`.`user_id`)}}} is
duplicate ({{{SUM(`study_league_point`.`point`)}}} too).
my desired query is like this:
{{{
COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
SUM(`study_league_point`.`point`) AS `point`,
(
point / no_members
) AS `avg_point`,
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34206>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Mohammad99k | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mohammad99k):
* component: Uncategorized => Database layer (models, ORM)
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Mohammad99k | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mohammad99k):
* type: Cleanup/optimization => Bug
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:2>
Django
-------------------------------------+-------------------------------------
| karimi
Type: Bug | Status: assigned
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mohammad99k):
* owner: nobody => Mohammad karimi
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Mohammad99k | Owner: Mohammad
| karimi
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => invalid
Comment:
Thanks for this ticket, however, as far as I'm aware, your proposition is
not doable as aliases cannot be created and used in the same `SELECT`
clause (see also #10060 and
[https://docs.djangoproject.com/en/4.1/topics/db/aggregation/ docs]). If
you're having trouble getting the result you want, see
TicketClosingReasons/UseSupportChannels for ways to get help.
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Mohammad99k | Owner: Mohammad
| karimi
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: invalid =>
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Mohammad99k | Owner: Mohammad
| karimi
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mohammad99k):
Replying to [comment:4 Mariusz Felisiak]:
> Thanks for this ticket, however, as far as I'm aware, your proposition
is not doable as aliases cannot be created and used in the same `SELECT`
clause (see also #10060 and
[https://docs.djangoproject.com/en/4.1/topics/db/aggregation/ docs]). If
you're having trouble getting the result you want, see
TicketClosingReasons/UseSupportChannels for ways to get help.
How can I fixed this issue?
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Mohammad99k | Owner: Mohammad
| karimi
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: annotate | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
Please use one of
[https://code.djangoproject.com/wiki/TicketClosingReasons/UseSupportChannels
support channels] for support questions.
--
Ticket URL: <https://code.djangoproject.com/ticket/34206#comment:7>