[Django] #27727: Exclude with many condition in the same related field's fields
Django
-------------------------------------+-------------------------------------
Reporter: Mounir | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 1.10
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When trying to exclude with many conditions in the same related field the
generated query (tested in Postgresql) will not be the desired one.
E.g. If I have a model Student and a model Teacher, Student have a
ForeignKey to Teacher model.
{{{
Teacher.objects.exclude(student__age=20, student__note__lt=5).query
}}}
Which will results in this query:
{{{
SELECT "teachers_teacher"."id", "teachers_teacher"."name" FROM
"teachers_teacher" WHERE NOT ("teachers_teacher"."id" IN (SELECT
U1."teacher_id" AS Col1 FROM "teachers_student" U1 WHERE U1."age" = 20)
AND "teachers_teacher"."id" IN (SELECT U1."teacher_id" AS Col1 FROM
"teachers_student" U1 WHERE U1."note" < 5))
}}}
I think the right SQL will be the following:
{{{
SELECT "teachers_teacher"."id", "teachers_teacher"."name" FROM
"teachers_teacher" WHERE NOT ("teachers_teacher"."id" IN (SELECT
U1."teacher_id" AS Col1 FROM "teachers_student" U1 WHERE U1."age" = 20 AND
U1."note" < 5))
}}}
Even if the fix will be need a lot of work, I think it's better to mention
this in the exclude documentation.
--
Ticket URL: <https://code.djangoproject.com/ticket/27727>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Mounir | Owner: nobody
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
Comment:
I believe that's a duplicate of #14645.
[https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-
multi-valued-relationships The documentation] already contains a note
about it.
--
Ticket URL: <https://code.djangoproject.com/ticket/27727#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: Mounir | Owner: nobody
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mounir):
Thank you and sorry for the duplicate, I would suggest to mention this in
the documentation of the
[https://docs.djangoproject.com/en/1.10/ref/models/querysets/#django.db.models.query.QuerySet.exclude
exclude]
Replying to [comment:1 Tim Graham]:
> I believe that's a duplicate of #14645.
[https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-
multi-valued-relationships The documentation] already contains a note
about it.
--
Ticket URL: <https://code.djangoproject.com/ticket/27727#comment:2>