Аннотировать QuerySet с первым значением упорядоченной связанной модели

У меня есть QuerySet некоторых объектов. Для каждого из них я хочу аннотировать минимальное значение связанной модели (объединенной на нескольких условиях, упорядоченных по дате). Я могу выразить свои желаемые результаты аккуратно в SQL, но мне интересно, как перевести на Django ORM.

Фон

Скажем, что у меня есть две связанные модели: Book и BlogPost, каждая из которых имеет внешний ключ для Author:

class Book(models.Model):
    title = models.CharField(max_length=255)
    genre = models.CharField(max_length=63)
    author = models.ForeignKey(Author)
    date_published = models.DateField()

class BlogPost(models.Model):
    author = models.ForeignKey(Author)
    date_published = models.DateField()

Я пытаюсь найти первую книгу тайн, которую опубликовал данный автор после каждого сообщения в блоге, которое они пишут. В SQL это может быть достигнуто красиво с помощью окна.

Рабочее решение в PostgreSQL 9.6

WITH ordered AS (
  SELECT blog_post.id,
         book.title,
         ROW_NUMBER() OVER (
            PARTITION BY blog_post.id ORDER BY book.date_published
         ) AS rn
    FROM blog_post
         LEFT JOIN book ON book.author_id = blog_post.author_id
                       AND book.genre = 'mystery'
                       AND book.date_published >= blog_post.date_published
)
SELECT id,
       title
  FROM ordered
 WHERE rn = 1;

Перевод на Django ORM

В то время как вышеупомянутый SQL подходит для моих потребностей (и я мог бы использовать необработанный SQL, если это необходимо), мне любопытно, как это сделать в QuerySet. У меня есть существующий QuerySet, где я хотел бы еще больше аннотировать его

books = models.Book.objects.filter(...).select_related(...).prefetch_related(...)
annotated_books = books.annotate(
    most_recent_title=...
)

Я знаю, что Django 2.0 поддерживает функции окна, но сейчас я на Django 1.10.

Попытка решения

Сначала я построил объект Q для фильтрации до тайных книг, опубликованных после публикации в блоге.

published_after = Q(
    author__book__date_published__gte=F('date_published'),
    author__book__genre='mystery'
)

Отсюда я попытался объединить django.db.models.Min и дополнительные объекты F для достижения моих желаемых результатов, но без успеха.

Примечание. Django 2.0 вводит выражения окна, но в настоящее время я на Django 1.10, и мне любопытно, как это можно сделать с доступными там функциями QuerySet.

Ответ 1

Возможно, использование .raw не такая уж плохая идея. Проверяя код Window class, мы видим, что по существу составлен SQL-запрос для достижения "Windowing".

Простым выходом может быть использование модуля architect, который может добавить функциональность раздела для PostgreSQL в соответствии с документация.

Другой модуль, который утверждает, что добавляет функциональность Window к Django < 2.0 является django-query-builder, который добавляет partition_by() queryset и может использоваться с order_by:

query = Query().from_table(
    Order,
    ['*', RowNumberField(
              'revenue', 
              over=QueryWindow().order_by('margin')
                                .partition_by('account_id')
          )
    ]
)
query.get_sql()
# SELECT tests_order.*, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY margin ASC) AS revenue_row_number
# FROM tests_order

Наконец, вы всегда можете скопировать исходный код класса Window в свой проект или использовать this alternate Код класса окна.

Ответ 2

Ваша очевидная проблема заключается в том, что Django 1.10 слишком устарел, чтобы нормально обрабатывать функции окна (которые были уже очень давно).

Эта проблема исчезает, если вы переписываете свой запрос без функции окна.

3 эквивалентных запроса

Какой из них наиболее быстрый, зависит от доступных индексов и распределения данных. Но каждый из них должен быть быстрее вашего оригинала.

1. С DISTINCT ON:

SELECT DISTINCT ON (p.id)
       p.id, b.title
FROM   blog_post p
LEFT   JOIN book b ON b.author_id = p.author_id
                  AND b.genre = 'mystery'
                  AND b.date_published >= p.date_published
ORDER  BY p.id, b.date_published;

Связанный с подробным объяснением:

Выберите первую строку в каждой группе GROUP BY?

2. С LATERAL подзапросом (требуется Postgres 9.3 или новее):

SELECT p.id, b.title
FROM   blog_post p
LEFT   JOIN LATERAL (
   SELECT title
   FROM   book 
   WHERE  author_id = p.author_id
   AND    genre = 'mystery'
   AND    date_published >= p.date_published
   ORDER  BY date_published
   LIMIT  1
   ) b ON true;
-- ORDER BY p.id  -- optional

Связанный с подробным объяснением:

В чем разница между LATERAL и подзапросом в PostgreSQL?

3. Или проще, но с коррелированным подзапросом:

SELECT p.id
     ,(SELECT title
       FROM   book 
       WHERE  author_id = p.author_id
       AND    genre = 'mystery'
       AND    date_published >= p.date_published
       ORDER  BY date_published
       LIMIT  1)
FROM   blog_post p;
-- ORDER BY p.id  -- optional

Каждый должен быть легко переведен в синтаксис Django. Вы также можете просто использовать необработанный SQL, который все равно отправляется на сервер Postgres.