Annotate QuerySet with first value of ordered related model

Perhaps using .raw isn't such a bad idea. Checking the code for Window class we can see that essentially composes an SQL query to achieve the "Windowing".

An easy way out may be the usage of the architect module which can add partition functionality for PostgreSQL according to the documentation.

Another module that claims to inject Window functionality to Django < 2.0 is the django-query-builder which adds a partition_by() queryset method and can be used with 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

Finally, you can always copy the Window class source code in your project or use this alternate Window class code.

Your apparent problem is that Django 1.10 is too old to handle window functions properly (which have been around for a very long time already).

That problem goes away if you rewrite your query without window function.

3 equivalent queries

Which of them is fastest depends on available indexes and data distribution. But each of them should be faster than your original.

1. With DISTINCT ON:

SELECT DISTINCT ON (p.id)       p.id, b.titleFROM   blog_post pLEFT   JOIN book b ON b.author_id = p.author_id                  AND b.genre = 'mystery'                  AND b.date_published >= p.date_publishedORDER  BY p.id, b.date_published;

Related, with detailed explanation:

• Select first row in each GROUP BY group?

2. With a LATERAL subquery (requires Postgres 9.3 or later):

SELECT p.id, b.titleFROM   blog_post pLEFT   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

Related, with detailed explanation:

• What is the difference between LATERAL and a subquery in PostgreSQL?

3. Or simpler, yet, with a correlated subquery:

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

Each should be translated easily to Django syntax. You might also just use the raw SQL, that's what is sent to the Postgres server anyway.