#33929: Field Reference in FilteredRelation Does Not Recognize Previously
Defined
FilteredRelation
-------------------------------------+-------------------------------------
Reporter: Matt | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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"),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/33929>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070182a91335db-7b51b27c-9705-47b7-aa12-0f00dab94898-000000%40eu-central-1.amazonses.com.
