Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#29196 closed Bug (invalid)

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

Reported by: Ivaylo Donchev Owned by: nobody
Component: Uncategorized Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by 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 ;)

Change History (3)

comment:1 Changed 3 years ago by Ivaylo Donchev

Description: modified (diff)

comment:2 Changed 3 years ago by Simon Charette

Resolution: invalid
Status: newclosed

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

comment:3 in reply to:  2 Changed 3 years ago 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 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.

Note: See TracTickets for help on using tickets.
Back to Top