Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined.

[Django]

#33682: Clarify using distinct() with related fields that have Meta.ordering
defined.
-+-
 Reporter:  Robert Leach |Owner:  anegawa-j
 Type:   |   Status:  assigned
  Cleanup/optimization   |
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 anegawa-j):

 * owner:  nobody => anegawa-j
 * status:  new => assigned


-- 
Ticket URL: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/010701847e596807-3fe95f9a-de24-42b6-8233-bf30dc883d98-00%40eu-central-1.amazonses.com.

Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined.

[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
--+

Comment (by Carlton Gibson):

 I think Mariusz' suggestion is more appropriate. I don't think the more
 discursive "gotcha"-type content fits in the reference documentation,
 rather than a more tutorial like piece, where it would be better. (Perhaps
 a blog post if there's no spot we yet discuss this in that kind of
 context.)

 I think the **solution** here comes back to Simon's comment:1…

 > ...error out loudly so at least users are not presented this cryptic
 error.

 If we could catch this and present an error showing the field mismatch
 folks would be able to correct at source. (That's totally Blue Skies — no
 idea how easy building a better error message is here.)

 (I think like Mariusz, I'd lean heavily towards the ''raise'' option,
 rather than trying to implicitly do/guess the right thing™ here.)

 HTH

-- 
Ticket URL: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070180bc5d60f4-74dd7b9f-ffd6-44f4-9bf9-2d6c1ba32d57-00%40eu-central-1.amazonses.com.

Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined.

[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):

 * cc: Carlton Gibson (added)


Comment:

 It seems we need a second option about the wording.

-- 
Ticket URL: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070180bc066761-a77e3b13-b9e4-4a75-b478-5fd457d47f05-00%40eu-central-1.amazonses.com.

Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined.

[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
--+

Comment (by Robert Leach):

 OK, I just learned that you **cannot** add something like `blog__name` to
 Entry's `Meta.ordering`.  Still learning...  So...

 > If all you want is to make a set of query results distinct without
 changing the ordering, note you must explicitly "re-"add the otherwise
 over-ridden fields defined in Meta.ordering. But be careful, if you simply
 add those fields, you can run afoul of the matching fields requirement
 between order_by and distinct. The field(s) defined in Meta.ordering can
 include a foreign key (via `ForeignKey`, `ManyToManyField`,
 `OneToManyField`, etc. fields), which will resolve differently in order_by
 (to the related model's Meta.ordering field(s)) and distinct (the _id) and
 the fields will no longer match between the 2 expressions.
 >
 > Fields of related models cannot be added to a Model's `Meta.ordering`
 (e.g. you cannot add `blog__name` to `Entry.Meta.ordering`), so to avoid
 the order_by versus distinct matching field gotcha **and** retain the
 default ordering in this instance, there are no shortcuts to only apply
 distinct to an existing query without explicitly re-applying the default
 ordering and resolving related model objects to database fields.

-- 
Ticket URL: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070180b94c2f6b-33ef910a-def4-40f6-993d-5957f50bd839-00%40eu-central-1.amazonses.com.

Re: [Django] #33682: Clarify using distinct() with related fields that have Meta.ordering defined.

[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
--+

Comment (by Robert Leach):

 That's definitely better, as it directly references Meta.ordering, which
 is a great clue to what the implications are.  What do you think about
 **adding** something like:

 > If all you want is to make a set of query results distinct without
 changing the ordering, note you must explicitly "re-"add the otherwise
 over-ridden fields defined in Meta.ordering.  But be careful, if you
 simply add those fields, you can run afoul of the matching fields
 requirement between order_by and distinct.  The field(s) defined in
 Meta.ordering can include a foreign key, which will resolve differently in
 order_by (to the related model's Meta.ordering field(s)) and distinct (the
 `_id`) and the fields will no longer match between the 2 expressions.
 >
 > But this gotcha can be entirely avoided by only ever using real database
 fields in every `Meta.ordering`, `order_by`, and `distinct` list instead
 of adding Django's related model objects (e.g. `ForeignKey`,
 `ManyToManyField`, `OneToManyField`, etc.) so as not to rely on Django's
 differing related-model-object to database-field resolving mechanisms
 (e.g. instead of adding `blog` to `Entry.Meta.ordering`, add
 `blog__name`).

 ...but like I said, all the info is there to either put it together and
 anticipate it (albeit requiring a fairly deep understanding and more
 experience to achieve those insights), or to interpret the exception.  I
 just feel like piecing together this (what I would consider common) use-
 case, may make it clearer to those who are endeavoring into their first
 big Django project and applying complex understandings of and experiences
 with other programming languages.

 Whatever edit gets included, I would like to say that I very much
 appreciate your attention to this topic.

-- 
Ticket URL: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070180b8e49e3b-33580446-18f5-4a87-8680-cf6eb6733b0a-00%40eu-central-1.amazonses.com.

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 table

[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 `. 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: 
Django 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/01070180b6a147d2-3aebc40b-c4c0-47ba-a132-cff0299d93b3-00%40eu-central-1.amazonses.com.