Repository: django/django
File: tests/queries/test_qs_combinators.py (Python)

Code before:
    def test_union_in_subquery_related_outerref(self):
        e1 = ExtraInfo.objects.create(value=7, info="e3")
        e2 = ExtraInfo.objects.create(value=5, info="e2")
        e3 = ExtraInfo.objects.create(value=1, info="e1")
        Author.objects.bulk_create(
            [
                Author(name="a1", num=1, extra=e1),
                Author(name="a2", num=3, extra=e2),
                Author(name="a3", num=2, extra=e3),
            ]
        )
        qs1 = ExtraInfo.objects.order_by().filter(value=OuterRef("num"))
        qs2 = ExtraInfo.objects.order_by().filter(value__lt=OuterRef("extra__value"))
        qs = (
            Author.objects.annotate(
                info=Subquery(qs1.union(qs2).values("info")[:1]),
            )
            .filter(info__isnull=False)
            .values_list("name", flat=True)
        )
        self.assertCountEqual(qs, ["a1", "a2"])
        # Combined queries don't mutate.
        self.assertCountEqual(qs, ["a1", "a2"])

    @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
    def test_union_in_with_ordering(self):
        qs1 = Number.objects.filter(num__gt=7).order_by("num")
        qs2 = Number.objects.filter(num__lt=2).order_by("num")
        self.assertNumbersEqual(
            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
            [2, 3, 4, 5, 6, 7],
            ordered=False,
        )

    @skipUnlessDBFeature(
        "supports_slicing_ordering_in_compound", "allow_sliced_subqueries_with_in"
    )
    def test_union_in_with_ordering_and_slice(self):
        qs1 = Number.objects.filter(num__gt=7).order_by("num")[:1]
        qs2 = Number.objects.filter(num__lt=2).order_by("-num")[:1]
        self.assertNumbersEqual(
            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
            [0, 2, 3, 4, 5, 6, 7, 9],
            ordered=False,
        )

    def test_count_union(self):
        qs1 = Number.objects.filter(num__lte=1).values("num")
        qs2 = Number.objects.filter(num__gte=2, num__lte=3).values("num")
        self.assertEqual(qs1.union(qs2).count(), 4)


Review comment (suggestion):
Spent 90% of the time trying to come up with a semi-realistic use case with the currently existing models.

Reviewers might find [this real-life example easier to grok](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/5)

Code after:
        self.assertCountEqual(
            ReservedName.objects.annotate(
                number=Subquery(qs1.union(qs2).values("num")),
            )
            .filter(number__isnull=False)
            .values_list("order", flat=True),
            [8, 1],
        )

    @skipUnlessDBFeature("supports_select_intersection")
    def test_intersection_in_nested_subquery(self):
        tag = Tag.objects.create(name="tag")
        note = Note.objects.create(tag=tag)
        annotation = Annotation.objects.create(tag=tag)
        tags = Tag.objects.order_by()
        tags = tags.filter(id=OuterRef("tag_id")).intersection(
            tags.filter(id=OuterRef(OuterRef("tag_id")))
        )
        qs = Note.objects.filter(
            Exists(
                Annotation.objects.filter(
                    Exists(tags),
                    notes__in=OuterRef("pk"),
                )
            )
        )
        self.assertIsNone(qs.first())
        annotation.notes.add(note)
        self.assertEqual(qs.first(), note)

    def test_union_in_subquery_related_outerref(self):
        e1 = ExtraInfo.objects.create(value=7, info="e3")
        e2 = ExtraInfo.objects.create(value=5, info="e2")
        e3 = ExtraInfo.objects.create(value=1, info="e1")
        Author.objects.bulk_create(
            [
                Author(name="a1", num=1, extra=e1),
                Author(name="a2", num=3, extra=e2),
                Author(name="a3", num=2, extra=e3),
            ]
        )
        qs1 = ExtraInfo.objects.order_by().filter(value=OuterRef("num"))
        qs2 = ExtraInfo.objects.order_by().filter(value__lt=OuterRef("extra__value"))
        qs = (
            Author.objects.annotate(
                info=Subquery(qs1.union(qs2).values("info")[:1]),
            )
            .filter(info__isnull=False)
            .values_list("name", flat=True)
        )
        self.assertCountEqual(qs, ["a1", "a2"])