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 django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
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.
