With regard to the stunning silence you're witnessing, it's my guess that
you haven't made yourself clear enough in a concise way. The stackoverflow
post is large, and doesn't point out what's missing very clearly.
What is the SQL you want?
What is the SQL you're getting, and what is the queryset you're
constructing?
I **think** what you're trying to get to is this:
SELECT * FROM (
SELECT
t.field,
lag(t.other, 1) over ( .. ) as wlag
FROM T t
WHERE t.field = 1
) inn
WHERE wlag = 3;
That is, you want to be able to filter on annotations without copying the
annotation into the WHERE clause by wrapping with an outer query that does
the filtering. Is that correct?
On Thursday, 28 February 2019 23:27:07 UTC+11, Bernd Wechner wrote:
>
> I have a problem I've only been able to solvewhich bugs me. I've posted on
> the Django users list (to stunning silence) and on stackoverflow:
>
>
> https://stackoverflow.com/questions/54388936/using-django-window-functions-on-a-filtered-queryset
>
> to comparable silence. I'm rather convinced this can't be done in Django
> without raw SQL and that it is an integral part of Window function utility
> so I'd like to propose a native ORM based solution to the problem.
>
> To understand the context it will be necessary to read the post on Stack
> Overflow above, there seems little point in copying the text here.
>
> The proposal though is simple enough. It is that django.db.models.Subquery
> support the methods that django.db.models.QuerySet does, specifically SQL
> constructing methods like annotate() and filter() - I'm sure there are
> more.
>
> The idea is to make easily available a way of selecting from a subquery
> such that something akin to:
>
> SQ = Subquery(model.objects.filter(...))
>
> produces SQL in the form:
>
> SELECT ... FROM model WHERE ...
>
> and now:
>
> Q = SQ.filter(---)
>
> would produce SQL in the form:
>
> SELECT * FROM (SELECT ... FROM model WHERE ...) AS SQ WHERE ---
>
> Essentially permitting us to filter on the results of a Subquery.
>
> Again, this is crucial when using Window functions like LAG and LEAD over
> a filtered list of objects. The reasons for this are explained on Stack
> Overflow with samples of code and SQL.
>
> Of course, in spite of the stunning silence on the Django users list and
> Stackoverflow, there's a chance we can already do this in Django in a way
> that does not involve Raw SQL that I have not found yet, in which case
> apologies, this really is a support question for the Django users list. But
> I have looked hard and asked hard and searched hard and experimented hard
> and delved into the Django code quite hard and I'm fairly convinced it's
> not possible at present ... without constructing Raw SQL.
>
> Regards,
>
> Bernd.
>
>
>
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/6fff5aee-cd4d-467c-8e6d-4c59bf734547%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
