Opened 8 months ago

Closed 8 months ago

Last modified 8 months ago

#33598 closed Bug (fixed)

Using multiple FilteredRelation with different filters but for same relation is ignored.

Reported by: lind-marcus Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 4.0
Severity: Release blocker Keywords: FilteredRelation
Cc: Nick Pope Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by lind-marcus)

I have a relation that ALWAYS have at least 1 entry with is_all=True and then I have an optional entry that could have is_all=False but instead have zone set.

I'm trying to use FilteredRelation together with Case(When()) to ensure that it use the zone level one (if exist) and fall back on "All" if zone do not exist.

from django.db.models import FilteredRelation

qs.alias(
    relation_zone=FilteredRelation(
        "myrelation__nested",
        condition=Q(myrelation__nested__zone=F("zone"))
    ),
    relation_all=FilteredRelation(
        "myrelation__nested",
        condition=Q(myrelation__nested__is_all=True)
    ),
    price_zone=F("relation_zone__price")
).annotate(
    price_final=Case(
        When(
            price_zone__isnull=True,
            then=F("relation_all__price"),
        ),
        default=F("price_zone")
    )
)

I noticed that when using multiple FilteredRelation with the same relation (myrelation__nested) it actually just generates a single SQL JOIN (if inspecting the raw SQL) and ignores the other. So in this case if I do print(str(qs.query)) I would only see a join for relation_zone. Not for relation_all.

Is this intended behavior or should I be able to do the thing above?

Change History (7)

comment:1 Changed 8 months ago by lind-marcus

Description: modified (diff)

comment:2 Changed 8 months ago by Mariusz Felisiak

Cc: Nick Pope added
Severity: NormalRelease blocker
Triage Stage: UnreviewedAccepted

Thanks for the report! A regression test:

  • tests/filtered_relation/tests.py

    diff --git a/tests/filtered_relation/tests.py b/tests/filtered_relation/tests.py
    index 790a90d9e2..1208ddde5f 100644
    a b class FilteredRelationTests(TestCase): 
    211211            str(queryset.query),
    212212        )
    213213
     214    def test_multiple(self):
     215        qs = (
     216            Author.objects.annotate(
     217                book_title_alice=FilteredRelation(
     218                    "book", condition=Q(book__title__contains="Alice")
     219                ),
     220                book_title_jane=FilteredRelation(
     221                    "book", condition=Q(book__title__icontains="Jane")
     222                ),
     223            )
     224            .filter(name="Jane")
     225            .values("book_title_alice__title", "book_title_jane__title")
     226        )
     227        self.assertSequenceEqual(
     228            qs,
     229            [
     230                {
     231                    "book_title_alice__title": None,
     232                    "book_title_jane__title": "The book by Jane A",
     233                },
     234                {
     235                    "book_title_alice__title": None,
     236                    "book_title_jane__title": "The book by Jane B",
     237                },
     238            ],
     239        )
     240
    214241    def test_with_multiple_filter(self):
    215242        self.assertSequenceEqual(
    216243            Author.objects.annotate(

Regression in 0c71e0f9cfa714a22297ad31dd5613ee548db379
Reproduced at 1cf60ce6017d904024ee132f7edae0b4b821a954.

Last edited 8 months ago by Mariusz Felisiak (previous) (diff)

comment:3 Changed 8 months ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:4 Changed 8 months ago by Mariusz Felisiak

Has patch: set

comment:5 Changed 8 months ago by Carlton Gibson

Triage Stage: AcceptedReady for checkin

comment:6 Changed 8 months ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In fac662f:

Fixed #33598 -- Reverted "Removed unnecessary reuse_with_filtered_relation argument from Query methods."

Thanks lind-marcus for the report.

This reverts commit 0c71e0f9cfa714a22297ad31dd5613ee548db379.

Regression in 0c71e0f9cfa714a22297ad31dd5613ee548db379.

comment:7 Changed 8 months ago by Mariusz Felisiak <felisiak.mariusz@…>

In 7d540d67:

[4.0.x] Fixed #33598 -- Reverted "Removed unnecessary reuse_with_filtered_relation argument from Query methods."

Thanks lind-marcus for the report.

This reverts commit 0c71e0f9cfa714a22297ad31dd5613ee548db379.

Regression in 0c71e0f9cfa714a22297ad31dd5613ee548db379.
Backport of fac662f4798f7e4e0ed9be6b4fb4a87a80810a68 from main

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