Hello Alexandr, I agree with Anssi and you that adding a method to perform a subquery pushdown would be useful.
FWIW #24462 and #28333 are not the only tickets where that was proposed as a solution. Searching for "subquery" and "pushdown" should bring a few other tickets. There are also internal cases where the ORM performs automatically (e.g. in sql.Query.get_aggregation) so it might be worth trying to refactor these to use a common Query.as_subquery (better name welcome) first. There's an existing ticket to unify the handling of subqueries[0] that should be a good introduction to the changes require to add this method[0]. Regarding 3. I think we should simple raise a FieldError in this particular case to require other_field to be included in the .values(). We've tried automatically selecting fields in the past when they were missing and it lead to unexpected behavior[1]. Since this would be a new API we'd have the luxury on being really strict on supported use case at first at lax the requirements later on if we deem it appropriate. I would assume a .subquery() that isn't preceded by a .values() would result in a SELECT * FROM (...)? Cheers, Simon [0] https://code.djangoproject.com/ticket/20127 [1] https://code.djangoproject.com/ticket/14357 Le mardi 20 août 2019 14:08:03 UTC-4, Alexandr Aktsipetrov a écrit : > > Hi, > > I've been thinking what is the best way to address #28333 > <https://code.djangoproject.com/ticket/28333> - it is desirable to be > able to filter by Window expression in ORM. But that requires using > subqueries in sql. The ticket itself proposes doing so implicitly and > keeping the usual filter interface: > > qs.annotate(row=Window(expression=RowNumber())).filter(row__gt=1) > > But that raises the question - how the expression below should be > interpreted? > > qs.annotate(row=Window(expression=RowNumber())).filter(row__gt=1, id__gt=1) > > Should id__gt go to the inner or the outer query? More generally, is it OK > for a queryset to be dependent on the order of .filter() calls? I feel this > would be quite surprising. So it's probably better to make this operation > more explicit. > > It was suggested by Anssi Kääriäinen in #24462 > <https://code.djangoproject.com/ticket/24462> to add .subquery() method > to querysets. This seems like yet another use case! > > But I tried to implement it, and almost immediately found an ambiguous > case: > > qs.values('id').subquery().filter(other_field=...) > > We can't just apply filter to the outer query - it doesn't have > other_field anymore due to values() call. What to do? Reject such an > attempt? Apply values() only to the outer query, not the inner one? Apply > filter() to the inner one (this is probably the most surprising...)? > Something else? > > No doubt there are many more tricky questions about semantics of such a > method. But I'd like to start this discussion somewhere. :) > > So, a few questions: > > 1. Do you agree that adding QuerySet.subquery is worthwhile? > 2. Do you agree it is the best way to solve #28333 > <https://code.djangoproject.com/ticket/28333>? > 3. How should it behave, in the specific case above or maybe more > generally? > > Thanks. > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/b2a77caa-f5b4-4b33-83af-02da01bec1c9%40googlegroups.com.
