Generating lists/reports with in-line summaries in Django

Question

Hi there,

I am trying to write a view that will generate a report which displays all Items within my Inventory system, and provide summaries at a certain point. This report is purely just an HTML template by the way.

In my case, each Item is part of an Order. An Order can have several items, and I want to be able to display SUM based summaries after the end of each order.

So the report kind of looks like this:

Order #25        <Qty> <Qty Sold> <Cost> <Cost Value>
Some Item          2       1       29.99    29.99
Another Item       4       0       10.00    40.00
<Subtotal Line>    6       1       39.99    69.99
Order #26        <Qty> <Qty Sold> <Cost> <Cost Value>
... Etc, you get the point

Now, I'm perfectly capable of displaying all the values and already have a report showing all the Items, but I have no idea how I can place Subtotals within the report like that without doing alot of queries. The Quantity, Qty Sold, and Cost fields are just part of the Item model, and Cost Value is just a simple model function.

Any help would be appreciated. Thanks in advance :-)

Answer 1

You could compute the subtotals in Python in the Django view.

The sub-totals could be stored in instances of the Model object with an attribute indicating that it's a sub-total. To keep the report template simple you could insert the sub-total objects in the right places in the result list and use the sub-total attribute to render the sub-total lines differently.

Answer 2

Subtotals are SELECT SUM(qty) GROUP BY order_number things.

They are entirely separate from a query to get details.

The results of the two queries need to be interleaved. A good way to do this is to create each order as a tuple ( list_of_details, appropriate summary ).

Then the display is easy

{% for order in orderList %}
    {% for line in order.0 %}
        {{ line }}
    {% endfor %}
    {{ order.1 }}
{% endfor %}

The hard part is interleaving the two queries.

details = Line.objects.all()
ddict = defaultdict( list )
for d in details:
    ddict[d.order_number].append(d)

interleaved= []
subtotals = ... Django query to get subtotals ... 
for s in subtotals:
    interleaved.append( ( ddict[s.order], s.totals ) )

This interleaved object can be given to your template for rendering.

Answer 3

Assuming you're not going to use any order-specific fields, you could perform single DB query followed by some python calculations:

from itertools import groupby
items = OrderItem.objects.select_related('order').order_by('order').all() # order_by is essential
items_by_order = dict(groupby(items, lambda x: x.order))
for order, items in items_by_order:
    items_by_order[order]['subtotals'] = ... # calculate subtotals for all needed fields

This is more generic approach compared to using separeate SQL query for calculating subtotals which imposes liability of syncronising WHERE clauses on both queries. You can also use any agregate function, not only thoses available on DB side.