Filtering on a ForeignKey relation with the field it relates to is not possible

[Petter Friberg]

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.

[Simon Charette]

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 that db_constraint does more than what's actually documented to do.

[Petter Friberg]

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.