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

[Florian Dahms]

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.

[David Sanders]

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

👍

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

[Jacob Walls]

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:

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