Code

Opened 2 years ago

Closed 2 years ago

#18165 closed Bug (wontfix)

Ordering by related field creates duplicates in resultant querysets

Reported by: dokterbob Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: ordering, duplicates, related
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When specifying an ordering property in a Model's Meta class, based on a field in a model with a ForeignKey relation to this model, duplicate objects are returned in the resulting queryset.

A test application with failing unittest is attached to the report in a tarball. In summary, the following models and query code can be used to replicate this issue:

    class TestModel(models.Model):
        class Meta:
            ordering = ('testrelated__field', )


    class TestRelated(models.Model):
        field = models.IntegerField()

        testmodel = models.ForeignKey(TestModel)

Now the following behaviour can be observed:

    >>> o = TestModel()
    >>> o.save()
    >>> r1 = TestRelated(field=1, testmodel=o)
    >>> r2 = TestRelated(field=2, testmodel=o)
    >>> r1.save()
    >>> r2.save()
    >>> TestModel.objects.all()
    [<TestModel: 1>, <TestModel: 1>]

The behaviour has found to exist for the SQLite backend and might or might not occur with other database backends.

Attachments (1)

testorder.tbz2 (927 bytes) - added by dokterbob 2 years ago.
Test application with regression test, demonstrating the issue

Download all attachments as: .zip

Change History (4)

Changed 2 years ago by dokterbob

Test application with regression test, demonstrating the issue

comment:1 Changed 2 years ago by akaariai

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to wontfix
  • Status changed from new to closed

I believe this is a known wontfix issue. What you have in the DB is this:

TM - R1 - field = 1
   \
    \
     R2 - field = 2

And what you are trying to do is order TM on the related model's field attribute. Note that there are two values for that field, 1 and 2. So, you are ordering a single object on two different values! Django's answer to this is to return two times the same object. Other option would be to throw an error, as there really isn't any correct answer to this situation if you want to return one object at a time. The query is allowed because when combined with filtering, ordering on reverse-related fields can be useful.

Long story short: wontfix.

comment:2 Changed 2 years ago by dokterbob

  • Resolution wontfix deleted
  • Status changed from closed to reopened

@akaariai I agree on the difficulty of the issue. In my current situation the ordering attribute is indeed being used on the front-end together with filtering. However, in the Admin we have multiple records for the same object popping up - confusing me as a developer, let alone the user.

In any case it would make sense to:

  1. Document the current behaviour and complications (I would gladly offer my help for that - any help to get me started is appreciated though).
  2. Get a reference to the apparently existing duplicate of this one as I was not able to find the original issue after (what I would consider) a through search.
  3. Somehow, allow at least the Admin to automatically detect the situation and do a .distinct() after filtering, assuring only one record per object and thus a consistent user experience. Simply adding .distinct() to the Admin's queryset method might not do as it would degrade exactly the kind of filtering required in the current situation.

The bottomline in all this: make sure that, at least, we won't confuse the user/developer. Please let me know how you stand in this so I could consider opening relevant related ticket('s) and, in as far as possible 'solve' the issue.

comment:3 Changed 2 years ago by akaariai

  • Resolution set to wontfix
  • Status changed from reopened to closed

The problem is that this is mostly unsolvable. If you have objects TM1 and TM2, where TM1 has related model with field values 1 and 3, and TM2 has related model with field value 2, then what is the correct sort order assuming you can return just one TM1 and one TM2 object?

This is already documented: https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by

I am reclosing this as wontfix. If you want to further discuss this then django-users (or django-developers) mailing list is the right forum.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.