[Django] #36617: RelatedManager QuerySet with filters on the same relation do not add inner joins

[Django]

#36617: RelatedManager QuerySet with filters on the same relation do not add inner
joins
-------------------------------------+-------------------------------------
Reporter: Florian Dahms | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When using the QuerySet from a RelatedManager I cannot do subsequent
filters over the same relation.

Given I have these models:

{{{
class A(models.Model):
x = models.IntegerField()
pass


class B(models.Model):
foo = models.ManyToManyField(A, related_name='bars')
}}}

then I would expect the following tests to pass:


{{{
class ExampleTestCase(TestCase):
def setUp(self):
A.objects.create(x=1)
A.objects.create(x=2)
b = B.objects.create()
b.foo.set(A.objects.all())

def test_1(self):
self.assertEqual(B.objects.filter(foo__x=1).filter(foo__x=2).count(),
1)

def test_2(self):
self.assertEqual(A.objects.get(x=1).bars.filter(foo__x=2).count(),
1)
}}}

The QuerySet from {{{B.objects.filter(foo__x=1)}}} behaves different than
the one from {{{A.objects.get(x=1).bars}}} (I would expect them to behave
in the same way). In test_1, the SQL has two INNER JOINs and in test_2 it
is only one.
--
Ticket URL: <https://code.djangoproject.com/ticket/36617>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36617: RelatedManager QuerySet with filters on the same relation do not add inner
joins
-------------------------------------+-------------------------------------

Reporter: Florian Dahms | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid

Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by David Sanders):

* resolution: => invalid
* status: new => closed

Comment:

Hi there,

Thanks for the report though what you're describing is expected &
documented behaviour:
https://docs.djangoproject.com/en/5.2/topics/db/queries/#spanning-multi-
valued-relationships

👍
--
Ticket URL: <https://code.djangoproject.com/ticket/36617#comment:1>

[Django]

#36617: RelatedManager QuerySet with filters on the same relation do not add inner
joins
-------------------------------------+-------------------------------------
Reporter: Florian Dahms | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Florian Dahms):

Thanks for the reply. The documentation suggests that independent filters
would always lead to the more permissive query behavior. So it is somehow
a caveat that when using the object property, the following filters will
behave more restrictive (maybe worth pointing out in the docs as a note).
--
Ticket URL: <https://code.djangoproject.com/ticket/36617#comment:2>

[Django]

#36617: RelatedManager QuerySet with filters on the same relation do not add inner
joins

-------------------------------+--------------------------------------

Reporter: Florian Dahms | Owner: (none)
Type: Bug | Status: closed

Component: Documentation | Version: 5.2
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

-------------------------------+--------------------------------------
Changes (by Jacob Walls):

* component: Database layer (models, ORM) => Documentation
* resolution: invalid => duplicate

Comment:

Good point, I don't think this can be understood from the docs alone.
Ultimately I think this is a duplicate of ticket:26379, which I will
reframe as a documentation update request.

In ticket:26379#comment:5:

> The first call to .filter() targets the join generated by the relation

So to get what you want, which is to have "independent filters" leading to
the "more permissive query" you could hack it in by making sure the "first
call to .filter()" is a dummy:

{{{#!py
>>> A.objects.get(x=1).bars.filter(foo__x=2)
<QuerySet []>
>>> A.objects.get(x=1).bars.filter().filter(foo__x=2)
<QuerySet [<B: B object (1)>]>
}}}

My understanding is that the "sticky" behavior is because there otherwise
*isn't* a way to write the restrictive query, since the first filter has
been abstracted away into the related manager.

I'll wager that's worth a couple words beneath the current example David
points to. Caveat: this is purely from reading ticket:26379, not speaking
from personal experience; this is worth verifying a bit further.
--
Ticket URL: <https://code.djangoproject.com/ticket/36617#comment:3>