Ordering by related field creates duplicates in resultant querysets

[Mathijs de Bruin]

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.

[Mathijs de Bruin]

Test application with regression test, demonstrating the issue

[Anssi Kääriäinen]

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.

[Mathijs de Bruin]

@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.

[Anssi Kääriäinen]

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.