Re: Window expression inside Subquery

It seems to be a bug in Django. Here's the ticket (https://code.djangoproject.com/ticket/29172) with

patch and tests if anyone is interested in this topic.

Cheers,

   Tom

Dne čtvrtek 1. března 2018 7:38:13 UTC+1 Tomáš Ehrlich napsal(a):

Hey folks,

I'm getting an AttributeError: 'NoneType' object has no attribute 'relabeled_clone'

when using Window expression inside Subquery:

Message.objects
.filter(pk__in=Subquery(
    Message.objects
    .annotate(latest_pk=Window(
        expression=FirstValue('pk'),
        partition_by=[F('conversation_id')],
        order_by=F('date').desc(),
    ))
    .values('latest_pk')
))

I would like to translate this SQL statement to Django ORM:

SELECT

  "conversations_message"."id",

  "conversations_message"."conversation_id",

  "conversations_message"."author_id",

  "conversations_message"."content",

  "conversations_message"."date"

FROM "conversations_message"

WHERE "conversations_message"."id" IN (

  SELECT first_value("id") OVER (PARTITION BY "conversation_id" ORDER BY "date" DESC)

  FROM conversations_message

)

I tested SQL statement and it works. I'm trying to select all conversations in DB

and prefetch latest message in each conversation.

I've found this note about using aggregates in Subqueries:

https://docs.djangoproject.com/en/2.0/ref/models/expressions/#using-aggregates-within-a-subquery-expression

but it doesn't seem to be related to my case.

I could however replace Window function with Max aggregate:

Message.objects.filter(pk__in=Subquery(
    Message.objects
        .order_by()
        .values('conversation')
        .annotate(latest=Max('id'))
        .values('latest')
    )
)

This works too, but I don't feel very comfortable using Max on `id`.

Related question: Is there a better way to prefetch latest related items? Both in Django and raw SQL.

Thanks in advance!

Cheers,

   Tom

--
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+unsubscribe@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/9558fa54-089d-4dd3-8e60-09fdf63712ec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.