id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35697	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:

{{{
#!python
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:

{{{
#!sql
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:

{{{
#!sql
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`."	Bug	closed	Database layer (models, ORM)	5.0	Normal	duplicate		Petter Friberg	Unreviewed	0	0	0	0	0	0