Generating lists/reports with in-line summaries in Django

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 :-)

From stackoverflow

• 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.

  Bartek : So you are saying basically loop through the created `report` queryset via Python, and when it recognizes I'm at a new order, I sum up the that group of items values, then inject the sub-total attribute within the list at the right point? I hope that makes sense, it's my best idea as of now.

  Akbar ibrahim : Conceptually, yes. The actual implementation could avoid having to detect a new order by using a dictionary of subtotal or some other implementation.

• 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.

  Bartek : Thanks a bunch! After playing around a bit with the Django ORM to get the correct GROUP BY functionality I pretty much have it working. Thanks again

• 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.