id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29196	Chaining multiple filters duplicates `INNER JOIN` for the final query	Ivaylo Donchev	nobody	"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 ;)
"	Bug	closed	Uncategorized	1.11	Normal	invalid			Unreviewed	0	0	0	0	0	0