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 ;)