Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined. (was: SQL generation bug in `.distinct()` when supplied fields go through multiple many-related tables)

[Django]

#33682: Clarify using distinct() with related fields that have Meta.ordering
defined.
--------------------------------------+------------------------------------
Reporter: Robert Leach | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: 3.2
Severity: Normal | Resolution:
Keywords: sql, distinct | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------------+------------------------------------
Changes (by Mariusz Felisiak):

* keywords: sql, distinct, => sql, distinct
* type: Bug => Cleanup/optimization
* component: Database layer (models, ORM) => Documentation
* stage: Unreviewed => Accepted

Comment:

I tried to clarify this, maybe:
{{{#!diff
diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt
index a9da1dcf7e..7c2c299b58 100644
--- a/docs/ref/models/querysets.txt
+++ b/docs/ref/models/querysets.txt
@@ -554,18 +554,24 @@ Examples (those after the first will only work on
PostgreSQL)::

.. note::
Keep in mind that :meth:`order_by` uses any default related model
ordering
- that has been defined. You might have to explicitly order by the
relation
- ``_id`` or referenced field to make sure the ``DISTINCT ON``
expressions
- match those at the beginning of the ``ORDER BY`` clause. For example,
if
- the ``Blog`` model defined an
:attr:`~django.db.models.Options.ordering` by
- ``name``::
+ that has been defined in
+ :attr:`Meta.ordering <django.db.models.Options.ordering>`. As a
+ consequence, ordering by a related field is resolved to the list of
fields
+ defined in a related ``Meta.ordering`` and may not be the same as a
+ relation ID field used by ``distinct()``. To avoid using
``Meta.ordering``
+ and make sure the ``DISTINCT ON`` expressions match those at the
beginning
+ of the ``ORDER BY`` clause, you can explicitly order by the relation
+ ``_id`` or referenced field. For example, if the ``Blog`` model
defined an
+ :attr:`~django.db.models.Options.ordering` by ``name``::

Entry.objects.order_by('blog').distinct('blog')

...wouldn't work because the query would be ordered by ``blog__name``
thus
mismatching the ``DISTINCT ON`` expression. You'd have to explicitly
order
by the relation ``_id`` field (``blog_id`` in this case) or the
referenced
- one (``blog__pk``) to make sure both expressions match.
+ one (``blog__pk``) to make sure both expressions match::
+
+ Entry.objects.order_by('blog_id').distinct('blog_id')

``values()``
~~~~~~~~~~~~
}}}
What do you think?

--
Ticket URL: <https://code.djangoproject.com/ticket/33682#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.