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
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/1aeaa0e3-99f1-4765-94a5-10f60ea11b61%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
