Re: [Django] #30389: Duplicate object when ordering through a foreign key
#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'
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:
Django
[Django] #30389: Duplicate object when ordering through a foreign key
#30389: Duplicate object when ordering through a foreign key
-+-
Reporter: Ajabep | Owner: nobody
Type: Bug| Status: new
Component: Database |Version:
layer (models, ORM)| Keywords: ordering, foreign
Severity: Normal | key
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 |Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-+-
When we are using an ordering through a foreign key, a same object can be
resolved several times.
=== PoC
Models
{{{
#!python
class Team(models.Model):
name = models.CharField(max_length=255, primary_key=True)
class Meta:
ordering = ['-persons__creationtime']
class Person(models.Model):
uuid = models.UUIDField(primary_key=True, default=uuid.uuid4,
editable=False)
creationtime = models.DateTimeField(auto_now_add=True)
team = models.ForeignKey(Team, on_delete=models.CASCADE)
class Meta:
default_related_name = 'persons'
}}}
Query
`Team.objects.filter(name="R")`
Buggy result
`, ]>`
The same team is selected 2 times.
Expected result
`]>`
Each teams (here, only 1) only 1 time.
Small analysis
By dumping the SQL request, we observe that the ordering is translated by
a join instruction.
`SELECT "poc_team"."name" FROM "poc_team" LEFT OUTER JOIN "poc_person" ON
("poc_team"."name" = "poc_person"."team_id") WHERE "poc_team"."name" = R
ORDER BY "poc_person"."creationtime" DESC`
Thus, if a `Team` object is linked to two `Person` objects, the `Team`
will be selected 2 times. If it is linked to 3 `Person`, the `Team` will
be selected 3 times.
This bug occurred also when you are using listing some teams, joined by a
ManyToMany relation.
=== Workaround, waiting a fix
To avoid this bug, while there is no official fix, use the `distinct()`
method:
`Team.objects.filter(name="R").distinct()`
--
Ticket URL:
Django
