Discussing potential QuerySet.subquery interface

[Alexandr Aktsipetrov]

Hi,

I've been thinking what is the best way to address #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 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?
3. How should it behave, in the specific case above or maybe more generally? 

Thanks.

[charettes]

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

[Alexandr Aktsipetrov]

Hello Simon,

thanks for the links.

I would assume a .subquery() that isn't preceded by a .values() would result in a SELECT * FROM (...)?

Maybe from implementation perspective it would be easier to copy all fields explicitly.