Chaining multiple filters duplicates `INNER JOIN` for the final query

[Ivaylo Donchev]

Let's say we have the following model structure:

    from django.db import models


    class Country(models.Model):
        name = models.CharField(max_length=255)


    class Group(models.Model):
        title = models.CharField(max_length=255)


    class Actor(models.Model):
        name = models.CharField(max_length=255)
    
        def __str__(self):
            return f'{self.name} - (id={self.id})'


    class Follower(models.Model):
        full_name = models.CharField(max_length=255)
        actor = models.ForeignKey(Actor,
                                  related_name='followers',
                                  on_delete=models.CASCADE)
        country = models.ForeignKey(Country,
                                    related_name='followers',
                                    on_delete=models.CASCADE)
        group = models.ForeignKey(Group,
                                  related_name='members',
                                  on_delete=models.CASCADE)

And I want to get all the actors who have a follower who has a relation BOTH to a country with 'name="Bulgaria"' AND a group with 'title="Programmers"'.

In the database I have:

Actor:

• id=1, name="Gerard Butler"

Country:

• id=1, name="Bulgaria"
• id=1, name="Germany"

Follower:

• id=1, full_name="Ivo", country="Bulgaria", actor=<actor with id=1>
• id=2, full_name="Martin", country="Germany", actor=<actor with id=1>

Group:

• id=1, title="Sportists"
• id=2, title="Programmers"

So when I execute the following query:

Actor.objects.filter(followers__country__name='Bulgaria', followers__group__title='Sportists')

I'm getting the right result: <QuerySet [<Actor: Gerard Butler - (id=1)>]>

But If I chain the two filters as follows:

Actor.objects.filter(followers__country__name='Bulgaria').filter(followers__group__title='Sportists')

I got a queryset of the duplicated object: <QuerySet [<Actor: Gerard Butler - (id=1)>, <Actor: Gerard Butler - (id=1)>]>

Is this an expected behaviour or a bug?

---

PS:
If it's going to be helpful:

The query, produced from the first queryset(Actor.objects.filter(followers__country__name='Bulgaria', followers__group__title='Sportists')) is:

SELECT "sample_actor"."id", "sample_actor"."name"
    FROM "sample_actor"
    INNER JOIN "sample_follower"
        ON ("sample_actor"."id" = "sample_follower"."actor_id")
    INNER JOIN "sample_country"
        ON ("sample_follower"."country_id" = "sample_country"."id")
    INNER JOIN "sample_group"
        ON ("sample_follower"."group_id" = "sample_group"."id")
    WHERE ("sample_country"."name" = Bulgaria AND "sample_group"."title" = Sportists)

And the query from the second queryset(Actor.objects.filter(followers__country__name='Bulgaria').filter(followers__group__title='Sportists')) is:

SELECT "sample_actor"."id", "sample_actor"."name"
    FROM "sample_actor"
    INNER JOIN "sample_follower"
        ON ("sample_actor"."id" = "sample_follower"."actor_id")
    INNER JOIN "sample_country"
        ON ("sample_follower"."country_id" = "sample_country"."id")
    INNER JOIN "sample_follower" T4
        ON ("sample_actor"."id" = T4."actor_id")
    INNER JOIN "sample_group" ON (T4."group_id" = "sample_group"."id")
    WHERE ("sample_country"."name" = Bulgaria AND "sample_group"."title" = Sportists)

So there's a duplicated INNER JOIN when chaining the filters. I hope this will help!

Greetings ;)

[Simon Charette]

This is expected filter(A, B) and filter(A).filter(B) don't behave the same when multi-valued relationships are involved ​as documented.

[Ivaylo Donchev]

Ah, I see. I hit the problem with this behaviour using https://github.com/carltongibson/django-filter - no matter what relation you have there will always be filter chaining if you have multiple filters at once. Anyway, it's my mistake. Thank you for the quick response!!!
Replying to Simon Charette:

This is expected filter(A, B) and filter(A).filter(B) don't behave the same when multi-valued relationships are involved ​as documented.