#30389: Duplicate object when ordering through a foreign key
-------------------------------------+-------------------------------------
Reporter: Ajabep | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version:
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: ordering, foreign | Triage Stage:
key | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* status: new => closed
* resolution: => invalid
Comment:
> By dumping the SQL request, we observe that the ordering is translated
by a join instruction.
Hello Ajabep, while this might be surprising if you are not familiar with
the ORM I'm afraid this isn't a bug; Django will translates all
`multivaluefield__value` lookups into `LEFT JOIN` and `order_by` is not an
exception.
The only other way to express this query would be perform a ''subquery
pushdown'' but it's unfortunately not possible to do it in a performant
way of all support database backends.
e.g.
{{{#!sql
SELECT "poc_team"."name"
FROM "poc_team"
WHERE "poc_team"."name" = 'R&D'
ORDER BY (
SELECT "poc_person"."creationtime"
FROM "poc_person"
WHERE "poc_team"."name" = "poc_person"."team_id"
ORDER BY "poc_person"."creationtime" DESC
LIMIT 1
)
}}}
If you really want to order by a multi valued relation without using
`distinct()` I suggest you manually perform the pushdown by ordering by a
[https://docs.djangoproject.com/en/2.2/ref/models/expressions/#django.db.models.Subquery
Subquery expression].
e.g.
{{{#!python
.order_by(
Subquery(
Person.objects.filter(
team=OuterRef('pk')
).order_by('-creationtime').values('creationtime')
)
)
}}}
Note that you might experience performance issues on some backends (older
versions of MySQL for example). So another alternative might be order by a
`Max('persons__creationtime')` annotation.
By the way please ensure
[https://docs.djangoproject.com/en/2.2/internals/contributing/bugs-and-
features/#reporting-bugs the problem you are encountering is a valid bug
before submitting a ticket through this tracker]. You'll likely get a
faster response through support channels and that'll reduce the ticket
triaging burden of contributors, thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/30389#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.8ae8de1894944e568657e4a93ea173b9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
