#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:

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

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
 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#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/069.98de5744e800c6f7466de41c8fb6c3c1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to