[Fixed]-Annotate QuerySet with first value of ordered related model

by

4đź‘Ť

âś…

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.

👤John Moutafis

3đź‘Ť

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.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;

Related, with detailed explanation:

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

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

Related, with detailed explanation:

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.

👤Erwin Brandstetter

Leave a comment