Hi all. Carlton suggested I should ask this here, as stated at https://code.djangoproject.com/ticket/29561#ticket
Please, I'd like to know how to filter using new django 2.0 window functions. Something similar to this beautiful and simple example <https://fle.github.io/detect-value-changes-between-successive-lines-with-postgresql.html> . Given a table db=> SELECT * FROM weather ORDER BY day DESC; daytemperaturerainy 2014-04-08 20.0 f 2014-04-07 20.0 f 2014-04-06 16.0 t 2014-04-05 16.0 t 2014-04-04 16.0 t 2014-04-03 22.0 f 2014-04-02 22.0 f 2014-04-01 22.0 t I'd like to show dates only if the temperature changed: daytemperature 2014-04-08 20.0 2014-04-06 16.0 2014-04-03 22.0 On pure PostgreSQL, this translates to: SELECT w1.day, w1.temperatureFROM (SELECT w2.day, w2.temperature, lead(w2.temperature) OVER (ORDER BY w2.day DESC) as prev_temp FROM weather w2 ORDER BY w2.day DESC) as w1WHERE w1.temp IS DISTINCT FROM w1.prev_tempORDER BY w1.day DESC; I could accomplish the inner query by using the new Window Functions: Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())) Now my problem is use this annotation to filter only when temperature differs from prev_temp (in order to accomplish something similar to the "temperature IS DISTINCT FROM prev_temp") When I try to use the available filters, the following errors occurs: Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').filter(temperature__ne=F('prev_temp')) Gives the error: FieldError: Unsupported lookup 'ne' for FloatField or join on the field not permitted. Another try: Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').filter(~Q(temperature=F('prev_temp')))) Gives the error: ProgrammingError: window functions are not allowed in WHERE It's in fact a PostgreSQL error, because the generated SQL query tries to pass the LAG function inside the where clause. How may I accomplish that, even if I have to use the extra fields, or even RawSQL class ? Right now I'm doing: with connection.cursor() as c: c.execute('SELECT q.day, q.temperature FROM (%s) AS q WHERE q.temperature IS DISTINCT FROM q.prev_temp' % str(Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').values('day','temperature', 'prev_temp').query)) result = c.fetchall() Important to note that I couldn't use the str(queryset.query) as parameter to the c.execute. I had to do a python replace. I.e, had to use ('...%s...' % parameter) instead of c.execute ('...%s...', [parameter]) Thank you all in advance. -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/c97d119a-45f2-4e02-ae77-486bf4327e62%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
