#35697 closed Bug (duplicate)
Filtering on a ForeignKey relation with the field it relates to is not possible
Reported by: | Petter Friberg | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 5.0 |
Severity: | Normal | Keywords: | |
Cc: | Petter Friberg | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
My use case is that I have a ForeignKey
relation with constraint=False
and then want to use rel_id=models.F("rel__pk")
to ensure that the value indeed exists in the related table.
Example:
from django.db import models class A(models.Model): ... class B(models.Model): a = models.ForeignKey(A, on_delete=models.CASCADE, constraint=False) B.objects.filter(a_id=models.F("a__pk"))
Here I would (optimally) want/expect the SQL to look like, though I would suspect that would have to be quite special cased:
SELECT "app_b"."id", "app_b"."a_id" FROM "app_b" INNER JOIN "app_a" ON ("app_b"."a_id" = "app_a"."id")
What I'm actually getting, I'm guessing that Django tries to be a bit too "smart" when resolving "a__pk"
here:
SELECT "app_b"."id", "app_b"."a_id" FROM "app_b" WHERE "app_b"."a_id" = ("app_b"."a_id")
Though I can get my INNER JOIN
by adding a .select_related
call. If I also pair that with calling e.g. .only
I can minimise the additional impact of the join. But I still thought it was worth filing this as I figured it was quite unexpected that "a__pk"
was resolved to "app_b"."a_id"
in this situation.
Note: I get the same SQL output when passing the primary key field explicitly, F("a__id")
in this case, instead of pk
.
Change History (2)
comment:1 by , 3 months ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
comment:2 by , 3 months ago
Thank you for your fast reply. I tried to look around for an already reported ticket but I see now that I should've spent more time searching.
This has come up a few times already so I'm going to close as a duplicate #33608 for the same reason as ticket:33838#comment:3.
To summarize when the
db_constraint
option was added there were no considerations beyond don't add the foreign key constraint baked into the change and since then no consensus has emerged in whether or not the ORM should avoid assuming integrity when optimizing references.In order to move this issue forward we likely need some wider reaching discussions on the subject as well as a PoC of what it might entail to limit these optimizations to
db_constraint=True
. Fixing one case at a time in a discrete manner (filter(id=F("ref__id")
in this case, improper JOIN in #33838) prevents us from ever be able to document thatdb_constraint
does more than what's actually documented to do.