Two Column Aggregate

Hi,

I wrote some code to generate sales statistics. Unfortunately, for the
part where I compute total costs of all goods, I currently have a loop
which causes a query to be executed for each order item. This makes
the statistics generation very slow.

I have found that I can run a single query to obtain this total cost, e.g.,

SELECT SUM(pre.cost_price * oi.quantity)
FROM priceregistryelement AS pre
JOIN priceregistry AS pr ON pr.id=pre.price_registry_id
JOIN product AS p ON p.id=pr.product_id
JOIN orderitem AS oi ON oi.product_id=p.id;

This reduces the time taken from more than two minutes to less than a
second. Great, except that it is raw SQL, and I will need this to be
combined with filters and such.

I tried using extra() with aggregate() as described here:
http://stackoverflow.com/questions/2541864/sumproduct-using-djangos-aggregation
Unfortunately, I ran into a "FieldError: Cannot resolve keyword
'item_total' into field".

My idea is to have an aggregate to handle this, perhaps:

total_costs = Order.objects.aggregate(total_costs=SumProduct(
'items__product__price_registry__price_registry_elements__cost_price',
'items__quantity',
))['total_costs']

However, this proposed aggregate has two columns. Is it feasible to
implement this, and if so, how should I go about it?

Thank you,
Eugene Wee

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.