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
33929	Field Reference in FilteredRelation Does Not Recognize Previously Defined FilteredRelation	Matt	nobody	"I suspect this may be the same root cause as https://code.djangoproject.com/ticket/33766, but the use-case here is different enough I thought I'd log a new ticket.

All this is using Django 4.0 or 4.1, on PostgreSQL.  I confess that I have not checked if other DB layers might generate correct SQL.

It appears that I cannot reference one FilteredRelation from another's condition without jumping through some hoops.  Starting with the following example models:
{{{
from django.db import models

class A(models.Model):
    ...

class B(models.Model):
    a = models.ForeignKey(""A"", on_delete=models.CASCADE)
    complete = models.BooleanField(default=False)

class C(models.Model):
    a = models.ForeignKey(""A"", on_delete=models.CASCADE)
    b = models.OneToOneField(""B"", blank=True, null=True, on_delete=models.CASCADE)
    complete = models.BooleanField(default=False)
}}}

Now suppose that I want a count of incomplete B, and also incomplete C, but only when related to an incomplete B.
If I were writing SQL myself, I’d write this as:
{{{
SELECT COUNT(b.id) as b_count, COUNT(c.id) as c_count
FROM a
LEFT JOIN b ON b.a_id = a.id AND NOT b.complete
LEFT JOIN c ON c.a_id = a.id AND c.b_id = b.id AND NOT c.complete
}}}

Now, the below queryset very nearly works:
{{{
A.objects.annotate(
    binc=FilteredRelation(""b"", condition=Q(b__complete=False)),
    cinc=FilteredRelation(""c"", condition=Q(c__b=F(""binc__pk""), c__complete=False)),
    b_count=Count(""binc""),
    c_count=Count(""cinc""),
)
}}}

Unfortunately this uses an incorrect table alias into the `cinc` FilteredRelation, where I tried to reference `F(""binc__pk"")`.  If I try to execute it, I get
{{{
django.db.utils.ProgrammingError: missing FROM-clause entry for table ""t4""
LINE 1: ...(""a"".""id"" = cinc.""a_id"" AND ((cinc.""b_id"" = (T4.""id"") A…
}}}

There is a workaround: I can force the correct identifier using RawSQL, and use this, which provides correct results:

{{{
A.objects.annotate(
    binc=FilteredRelation(""b"", condition=Q(b__complete=False)),
    cinc=FilteredRelation(""c"", condition=Q(c__b=RawSQL(""binc.id"", ()), c__complete=False)),
    b_count=Count(""binc""),
    c_count=Count(""cinc""),
)
}}}"	Bug	closed	Database layer (models, ORM)	4.1	Normal	duplicate			Unreviewed	0	0	0	0	0	0