python - 用有序相关模型的第一个值注释 QuerySet

Question

我有一些对象的 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 功能来实现这一点。

Answer 1

也许使用.raw 并不是一个坏主意。检查 Window class 的代码我们可以看到，本质上是组成一个SQL查询来实现“Windowing”。

一个简单的方法可能是使用 architect可以根据 the documentation 为 PostgreSQL 添加分区功能的模块.

另一个声称向 Django < 2.0 注入(inject) Window 功能的模块是 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。窗口类代码。