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+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.