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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/29338>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Comment (by Tim Graham):
Are you sure that generating some sensible SQL for this queryset is
possible?
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:1>
Comment (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).
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:2>
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:3>
* cc: Jeff (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:4>
* cc: Can Sarıgöl (added)
* has_patch: 0 => 1
Comment:
hi, I thought that we can use union queries in subqueries by replacing the
alias from origin query to union queries. I've pushed a commit. if this
approach is ok, I can add other tests and go further?
[https://github.com/django/django/pull/11152 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:5>
Comment (by Can Sarıgöl):
> {{{
> 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])
> )
> }}}
I change the example like this:
{{{
cls = Document.objects.filter(
checklist__isnull=False,
).values('checklist__customer', 'created')
ots = Document.objects.filter(
ownershiptransfer__isnull=False,
).values('ownershiptransfer__ship_target__contact', 'created')
return self.annotate(
owner=Subquery(cls.union(ots).filter(part=OuterRef('id')).values('owner')[:1])
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:6>
* status: new => assigned
* owner: nobody => Can Sarıgöl
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:7>
Comment (by Can Sarıgöl):
I forgot it, thanks.
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:8>
* version: 2.0 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:9>
* owner: Can Sarıgöl => (none)
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:10>
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:11>
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:12>
Comment (by felixxm):
[https://github.com/django/django/pull/11692 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:13>
* owner: (none) => felixxm
* needs_better_patch: 1 => 0
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:14>
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:15>
* cc: InvalidInterrupt (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:16>
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:17>
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"30a01441347d5a2146af2944b29778fa0834d4be" 30a01441]:
{{{
#!CommitTicketReference repository=""
revision="30a01441347d5a2146af2944b29778fa0834d4be"
Fixed #29338 -- Allowed using combined queryset in Subquery.
Thanks Eugene Kovalev for the initial patch, Simon Charette for the
review, and Chetan Khanna for help.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29338#comment:18>