Customized FROM clause/sort by computed annotation values

[stsch...@limbozz.com]

Hey Django users,

I'm building a quite large query set that makes use of subqueries and lots of annotations. Here's the relevant part:

queryset = StockItem.objects \

  .order_by() \

  .annotate(available_stock_count=Subquery(stock_booking_count, output_field=IntegerField())) \

  .annotate(required_stock_count=Subquery(required_stock_count, output_field=IntegerField())) \

  .annotate(booked_stock_count=Subquery(booked_stock_count, output_field=IntegerField()))

There's another annotation I need, and that's a computed value (mainly needed for sorting reasons):

  .annotate(predicted_stock_count=(

    F("available_stock_count") - (F("required_stock_count") + F("booked_stock_count"))

  ))

This works, however it only does because Django has an awesome smart query builder. ;-) Normally you can't use annotated fields (read: fields that are in the SELECT clause) for computing a new value in the same query. Django knows that and fills in the full subqueries again to make the computation happen.

Of course this increases the performance impact a lot. What you normally do in SQL to avoid that is something like this:

SELECT

  "inner".*,

  ("inner".x + "inner".y - "inner".u) computed_value

FROM (SELECT ... ALL THE HEAVY COMPUTATIONS) AS "inner"
ORDER BY computed_value ASC

Note the subquery after FROM. Everything from the inner query is forwarded, and a new value can be computed without issues, plus used for sorting.

Is this in any way possible with Django? I'm currently resorting to a raw SQL query that embeds str(queryset.query), but I lose all the benefits of having a real Django queryset, especially because I'm using REST Framework which highly depends on them.

Thanks!

Stefan Schindler