Josh,
Thanks very much for your contribution. Yes it is not concise, but I did
hope it is clear. Alas there is a contest between a well presented, well
researched, and complete question and a concise one. Sometimes the two
don't reconcile themselves well I admit.
Your supposition is almost right though:
1. It relates to more to a problem that DISTINCT and INNER JOIN produce
with Window functions. If you want DISTINCT tuples on a JOINed pair of
tables that is fine, but the minute you put a window function in because
each tuple gains a unique value before the DISTINCT is applied DISTINCT is
rendered functionally useless.
2. The only fix to that is to force DISTINCT to apply before the window
functions are added (i.e. before annotating).
3. Django seems not to have a way to do that, bar RAW SQL. And I would
argue it should.
To paraphrase your example and ignoring JOINS, in the interest of
simplicity and brevity:
In this query if multiple tuples that match the WHERE share the same value
of field1 then rows in the output are duplicated.
SELECT t.field1
FROM T t
WHERE t.field2 like "%x%"
In this query:
SELECT DISTINCT t.field1
FROM T t
WHERE t.field2 like "%x%"
the duplication is avoided. But in this query:
SELECT DISTINCT t.field1, row_number(t.other, 1) over ( .. ) as wrownum
FROM T t
WHERE t.field2 like "%x%"
The duplication is maintained. That is because each row receives a unique
value of wrownum, and DISTINCT is applied to the result! The way to fix
this is:
SELECT t.field1, row_number(t.other, 1) over ( .. ) as wrownum
FROM (
SELECT DISTINCT t.field1
FROM T t
WHERE t.field2 like "%x%"
) inn
And there seems no way in Django currently to do such a simple breakout
SELECT wrapper with Subqueries that I can find.
As an aside, if t.field1 is an object id, the above example actually causes
no problems, but when you do a join to any that that T has a ToMany
relationship with, then this problem emerged which is where I find it. I
get duplicate tuples with the same id because the LIKE matches more than
one of the related objects. That complicates the queries a little and is
well presented on StackOverflow.
My concern is it's not possible and would be if we supported perhaps a new
argument on filters like Wrap which if true wraps the SQL in a new Select.
BUt I'm not 100% sure there isn't an existing Django ORM option, I am just
at this point deeply suspicious there isn't one.
Your example below BTW is also a fine use case, and similarly not supported
currently in the ORM I fear. Unless (and I hope) I am mistaken.
Kind regards,
Bernd.
On Tuesday, 12 March 2019 20:29:16 UTC+11, Josh Smeaton wrote:
>
> 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 django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
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/01dd41b4-8ecc-4f83-89a6-aa635faa2aec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
