How to use FilteredRelation with OuterRef?

Question

I'm trying to use Django ORM to generate a queryset and I can't find how to use an OuterRef in the joining condition with a FilteredRelation.

What I have in Django

Main queryset

queryset = LineOutlier.objects.filter(report=self.kwargs['report_pk'], report__apn__customer__cen_id=self.kwargs['customer_cen_id']) \
                    .select_related('category__traffic') \
                    .select_related('category__frequency') \
                    .select_related('category__stability') \
                    .prefetch_related('category__traffic__labels') \
                    .prefetch_related('category__frequency__labels') \
                    .prefetch_related('category__stability__labels') \
                    .annotate(history=subquery)

The subquery

subquery = ArraySubquery(
            LineOutlierReport.objects
            .filter((Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)) & Q(id__in=last_5_reports_ids))
             .values(json=JSONObject(
                            severity='lineoutlier__severity',
                            report_id='id',
                            report_start_date='start_date',
                            report_end_date='end_date'
                            )
                    )
        )

The request can be executed, but the SQL generated is not exactly what I want :

SQL Generated

SELECT "mlformalima_lineoutlier"."id",
       "mlformalima_lineoutlier"."imsi",
       ARRAY(
        SELECT JSONB_BUILD_OBJECT('severity', V1."severity", 'report_id', V0."id", 'report_start_date', V0."start_date", 'report_end_date', V0."end_date") AS "json"
          FROM "mlformalima_lineoutlierreport" V0
          LEFT OUTER JOIN "mlformalima_lineoutlier" V1
            ON (V0."id" = V1."report_id")
         WHERE ((V1."imsi" = ("mlformalima_lineoutlier"."imsi") OR V1."id" IS NULL) AND V0."id" IN (SELECT DISTINCT ON (U0."id") U0."id" FROM "mlformalima_lineoutlierreport" U0 WHERE U0."apn_id" = 2 ORDER BY U0."id" ASC, U0."end_date" DESC LIMIT 5))
       ) AS "history",
  FROM "mlformalima_lineoutlier"

The problem here is that the OuterRef condition (V1."imsi" = ("mlformalima_lineoutlier"."imsi")) is done on the WHERE statement, and I want it to be on the JOIN statement

What I want in SQL

SELECT "mlformalima_lineoutlier"."id",
       "mlformalima_lineoutlier"."imsi",
       ARRAY(
        SELECT JSONB_BUILD_OBJECT('severity', V1."severity", 'report_id', V0."id", 'report_start_date', V0."start_date", 'report_end_date', V0."end_date") AS "json"
          FROM "mlformalima_lineoutlierreport" V0
          LEFT OUTER JOIN "mlformalima_lineoutlier" V1
            ON (V0."id" = V1."report_id" AND ((V1."id" IS NULL) OR V1."imsi" = ("mlformalima_lineoutlier"."imsi")))
         WHERE V0."id" IN (SELECT DISTINCT ON (U0."id") U0."id" FROM "mlformalima_lineoutlierreport" U0 WHERE U0."apn_id" = 2 ORDER BY U0."id" ASC, U0."end_date" DESC LIMIT 5))
       ) AS "history",
  FROM "mlformalima_lineoutlier"

What I tried in Django

I tried to use the FilteredRelation to change the JOIN condition, but I can't seem to use it in combination with an OuterRef

subquery = ArraySubquery(
            LineOutlierReport.objects
            .annotate(filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)))
            .filter(Q(id__in=last_5_reports_ids))
            .values(json=JSONObject(
                            severity='filtered_relation__severity',
                            report_id='id',
                            report_start_date='start_date',
                            report_end_date='end_date'
                            )
                    )
        )

I can't execute this query because of the following error

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

How can I modify my query to make it work ?

Answer 1

This looks like this Django bug. As a workaround you can annotate another column and reference it in the FilteredRelation, like so :

subquery = ArraySubquery(
        LineOutlierReport.objects
        .annotate(
            outer_imsi=OuterRef('imsi'),
            filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=F('outer_imsi')) | Q(lineoutlier__isnull=True)))
        .filter(Q(id__in=last_5_reports_ids))
        .values(json=JSONObject(
                        severity='filtered_relation__severity',
                        report_id='id',
                        report_start_date='start_date',
                        report_end_date='end_date'
                        )
                )
    )

That way you avoid OuterRef being processed inside FilteredRelation.