Nested OuterRef's Don't Seem to Use the Correct Table

[Jethro Muller]

Good day

I'm trying to create a query with nested Subqueries. It, unfortunately, doesn't seem to be generating the correct SQL. My assumption is that I'm doing something wrong. If anyone can see what the issue is, I'd appreciate feedback.

Models

class AdditionFeeTable(TimeStampedModel):
    network = models.ForeignKey(
        'sims.Network',
        related_name='additions_fees',
        on_delete=models.deletion.PROTECT

    )
    organisation = models.ForeignKey(
        'organisations.Organisation',
        blank=True,
        null=True,
        related_name='addition_fees',
        on_delete=models.deletion.PROTECT
    )
    amount = models.DecimalField(max_digits=10, decimal_places=2)

class Organisation(Organization):   # Has site foreign key - Site has networks many to many
    pass

Query

custom_addition_table = AdditionFeeTable.objects.filter(
    organisation_id=OuterRef(OuterRef('id')),
    network_id=OuterRef('network_id'),
)

addition_fees = AdditionFeeTable.objects.filter(
    Q(organisation_id=OuterRef('id')) | Q(organisation__isnull=True),
    network_id=OuterRef('site__networks__id'),
).annotate(
    custom_exists=Exists(custom_addition_table)
).exclude(
    custom_exists=True,
    organisation__isnull=True,
).values('amount')

Organisation.by_site(self.request.current_site).values(
    'site__networks__id',
).annotate(
    addition_fee_price=Subquery(addition_fees),
    network_name=F('site__networks__name'),
)

Generated SQL

SELECT 
  "organisations_organisation"."organization_ptr_id", 
  "organizations_organization"."slug", 
  "organizations_organization"."name", 
  (
    SELECT 
      V0."amount" 
    FROM 
      "fees_additionfeetable" V0 
    WHERE 
      (
        (
          V0."organisation_id" = (
            "organisations_organisation"."organization_ptr_id"
          ) 
          OR V0."organisation_id" IS NULL
        ) 
        AND V0."network_id" = ("sims_network"."id") 
        AND NOT (
          EXISTS(
            SELECT 
              U0."id", 
              U0."created", 
              U0."modified", 
              U0."network_id", 
              U0."organisation_id", 
              U0."amount" 
            FROM 
              "fees_additionfeetable" U0 
            WHERE 
              (
                U0."network_id" = (V0."network_id") 
                AND U0."organisation_id" = (V0."id") -- The issue is here. This should be: `AND U0."organisation_id" = ("organisations_organisation"."id")`
              )
          ) = true 
          AND V0."organisation_id" IS NULL
        )
      ) 
    ORDER BY 
      V0."modified" DESC, 
      V0."created" DESC
  ) AS "addition_fee_price", 
  "sims_network"."name" AS "network_name" 
FROM 
  "organisations_organisation" 
  INNER JOIN "core_site" ON (
    "organisations_organisation"."site_id" = "core_site"."site_ptr_id"
  ) 
  LEFT OUTER JOIN "sims_network" ON (
    "core_site"."site_ptr_id" = "sims_network"."site_id"
  ) 
  INNER JOIN "organizations_organization" ON (
    "organisations_organisation"."organization_ptr_id" = "organizations_organization"."id"
  ) 
WHERE 
  "organisations_organisation"."site_id" = 1 
LIMIT 
  25;

The issue is in the generated SQL and is highlighted by the comment in red lettering.

Thank you for any help

Jethro