#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&D")`


 ==== Buggy result

 `<QuerySet [<Team: Team object (R&D)>, <Team: Team object (R&D)>]>`

 The same team is selected 2 times.


 ==== Expected result

 `<QuerySet [<Team: Team object (R&D)>]>`

 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&D
 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&D").distinct()`

-- 
Ticket URL: <https://code.djangoproject.com/ticket/30389>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/049.2e019e538518147a8c12c183c45458ca%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to