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
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/4191ef47-c343-42c5-9fe2-f05d2abedbed%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
