[Django] #31963: Django: Nested OuterRef not woking to access grandparent's ID

[Django]

#31963: Django: Nested OuterRef not woking to access grandparent's ID
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
ashish141199 |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) | Keywords: OuterRef, QuerySet,
Severity: Normal | Django Filter, Annotation,
Triage Stage: | Database, ORM
Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Trying to access FeeAccount model's id using **OuterRef** in a **nested
subquery**, but **not immediate parent**. Throwing:

`ValueError: This queryset contains a reference to an outer query and may
only be used in a subquery.`

```
first_unpaid_fee_schedule_instalment =
Subquery(FeeScheduleInstalment.objects \
.annotate(
total_balance = Subquery(
FeeInstalmentTransaction.objects.filter(
account_id=OuterRef(OuterRef('id')), # not
working: need grand-parent's id here
instalment__schedule=OuterRef('id'),
#working: parent's id
) \
.values('balance') \
.annotate(total_balance=Sum('balance')) \
.values('total_balance')
)
) \
.values('id')[:1]
)

fee_accounts_with_first_unpaid = FeeAccount.objects \
.annotate(
first_unpaid_schedule =
first_unpaid_fee_schedule_instalment,
)
```

--
Ticket URL: <https://code.djangoproject.com/ticket/31963>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#31963: Django: Nested OuterRef not woking to access grandparent's ID
-------------------------------------+-------------------------------------

Reporter: ashish141199 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: OuterRef, QuerySet, | Triage Stage: Accepted
Django Filter, Annotation, |
Database, ORM |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by ashish141199):

* stage: Unreviewed => Accepted

Old description:

New description:

--

--
Ticket URL: <https://code.djangoproject.com/ticket/31963#comment:1>

[Django]

#31963: Django: Nested OuterRef not woking to access grandparent's ID
-------------------------------------+-------------------------------------

Reporter: Ashish Kulkarni | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: OuterRef, QuerySet, | Triage Stage:

Django Filter, Annotation, | Unreviewed
Database, ORM |

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* stage: Accepted => Unreviewed

Comment:

You should not accept your own tickets.

--
Ticket URL: <https://code.djangoproject.com/ticket/31963#comment:2>

[Django]

#31963: Nested OuterRef not woking to access grandparent's ID.

-------------------------------------+-------------------------------------
Reporter: Ashish Kulkarni | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 2.2

(models, ORM) | Resolution:
Severity: Normal | worksforme

Keywords: OuterRef, QuerySet, | Triage Stage:

Django Filter, Annotation | Unreviewed

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* keywords: OuterRef, QuerySet, Django Filter, Annotation, Database, ORM
=> OuterRef, QuerySet, Django Filter, Annotation
* status: new => closed
* resolution: => worksforme

Old description:

New description:

Trying to access `FeeAccount` model's id using **OuterRef** in a **nested
subquery**, but **not immediate parent**. Throwing:

`ValueError: This queryset contains a reference to an outer query and may
only be used in a subquery.`

{{{
first_unpaid_fee_schedule_instalment = Subquery(

FeeScheduleInstalment.objects.annotate(
total_balance = Subquery(
FeeInstalmentTransaction.objects.filter(
account_id=OuterRef(OuterRef('id')), # not working: need
grand-parent's id here
instalment__schedule=OuterRef('id'), #working: parent's id

).values('balance').annotate(total_balance=Sum('balance')).values('total_balance')
),
).values('id')[:1]
)

fee_accounts_with_first_unpaid = FeeAccount.objects.annotate(
first_unpaid_schedule = first_unpaid_fee_schedule_instalment,
)
}}}

--

Comment:

The described example works for me.

--
Ticket URL: <https://code.djangoproject.com/ticket/31963#comment:3>