Opened 6 weeks ago

Last modified 4 days ago

#29338 new Bug

Can't use OuterRef in union Subquery

Reported by: Matthew Pava Owned by: nobody
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords:
Cc: Jeff Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When you make a QuerySet using the union method or the | operator, the QuerySet passed into the union method cannot reference OuterRef even when wrapped with Subquery.

For example:

cls = Document.objects.filter(
    checklist__isnull=False,
    part=OuterRef('id')
).values('checklist__customer', 'created')

ots = Document.objects.filter(
    ownershiptransfer__isnull=False,
    part=OuterRef('id')
).values('ownershiptransfer__ship_target__contact', 'created')

return self.annotate(
    owner=Subquery(cls.union(ots).values('owner')[:1])
)

Returns this error:

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

I get the same error with this statement:

return self.annotate(
    owner=Subquery((cls | ots).values('owner')[:1])
)

(As an aside, I also get an error when I try to apply an order_by clause.)

Change History (4)

comment:1 Changed 6 weeks ago by Tim Graham

Are you sure that generating some sensible SQL for this queryset is possible?

comment:2 Changed 5 weeks ago by Matthew Pava

Yes, it is possible to generate SQL for this query. I tried it myself. ...I suppose "sensible" is a subjective term.

I just tried using an __in lookup, but that was getting to be a struggle.

Maybe Subquery wasn't originally intended to be used with unions, but I think it would be a good feature to have, especially in the discussion of CTEs (common table expressions).

comment:3 Changed 5 weeks ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:4 Changed 4 days ago by Jeff

Cc: Jeff added
Note: See TracTickets for help on using tickets.
Back to Top