[Django] #29196: Chaining multiple filters duplicates `INNER JOIN` for the final query

[Django]

#29196: Chaining multiple filters duplicates `INNER JOIN` for the final query
------------------------------------------+------------------------
Reporter: Ivaylo Donchev | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 1.11
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 |
------------------------------------------+------------------------
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 has 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 ;)

--
Ticket URL: <https://code.djangoproject.com/ticket/29196>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#29196: Chaining multiple filters duplicates `INNER JOIN` for the final query

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

Reporter: Ivaylo Donchev | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 1.11

Severity: Normal | Resolution:

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

--------------------------------+--------------------------------------
Description changed by Ivaylo Donchev:

Old description:

> {{{
> from django.db import models
>

> Greetings ;)

New description:

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

Greetings ;)

--

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

[Django]

#29196: Chaining multiple filters duplicates `INNER JOIN` for the final query

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

Reporter: Ivaylo Donchev | Owner: nobody

Type: Bug | Status: closed
Component: Uncategorized | Version: 1.11
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 Simon Charette):

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

Comment:

This is expected `filter(A, B)` and `filter(A).filter(B)` don't behave the
same when multi-valued relationships are involved
[https://docs.djangoproject.com/en/2.0/topics/db/queries/#lookups-that-
span-relationships as documented].

--
Ticket URL: <https://code.djangoproject.com/ticket/29196#comment:2>

[Django]

#29196: Chaining multiple filters duplicates `INNER JOIN` for the final query

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

Reporter: Ivaylo Donchev | Owner: nobody

Type: Bug | Status: closed
Component: Uncategorized | Version: 1.11
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 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 [comment:2 Simon Charette]:

> This is expected `filter(A, B)` and `filter(A).filter(B)` don't behave
the same when multi-valued relationships are involved
[https://docs.djangoproject.com/en/2.0/topics/db/queries/#lookups-that-
span-relationships as documented].

--
Ticket URL: <https://code.djangoproject.com/ticket/29196#comment:3>