Window expression inside Subquery

[Tomáš Ehrlich]

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

[Tomáš Ehrlich]

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):