[Django] #33929: Field Reference in FilteredRelation Does Not Recognize Previously Defined FilteredRelation

[Django]

#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 django-updates+unsubscr...@googlegroups.com.
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.