Display multiple related Django models in a table design problem

Question

Hi there, I'm struggling with the design of a django application. Given the following models:

class A(models.Model):
    name = models.CharField(max_length=255)

class B(models.Model):
    name = models.CharField(max_length=255)
    a = models.ForeignKey(A)

class C(models.Model):
    val = models.IntegerField()
    b = models.ForeignKey(B)

I would like a view/template that displays a HTML table that shows in the first column all A objects, in the second column all B objects (grouped by A) that are referenced to A and in the last column a sum of all val's of C objects that are referenced to each B. All of that with a sum for each A object. The following example shows what I'm looking for:

A1.name | B1.name [where FK to A1] | sum(C.val) [where FK to B1]
A1.name | B2.name [where FK to A1] | sum(C.val) [where FK to B2]
A1.name |                   Total  | sum(C.val) [where FK to Bx (all B that have FK to A1]
A2.name | B3.name [where FK to A2] | sum(C.val) [where FK to B3]
A2.name |                   Total  | sum(C.val) [where FK to Bx (all B that have FK to A2]

Can anyone give me an advice how to design such a problem (unfortunately, my code often ends up in quite a mess)?

Should I extend the model classes with appropriate methods? Do a custom query for the whole table data in the view? Just get all the objects via managers and do most of the things in the template?

Thanks for every answer.

Regards,

Luke.

Answer 1

Disclaimer: I'm a Django beginner myself, but if I've understood its basic concepts right, then:

Given that the view you want is - as you say - just a view of the underlying data, then you should do the sorting and grouping in the view module. I don't think you should mess with the model (which is just the data), nor with the template (which is just a layout of the view).

Answer 2

If you have that date model so you can fetch all bs then group them by a and calculate total amount. Code may look like this:

View:

from django.utils.itercompat import groupby

def view(request):
   bs = B.objects.all().annotate(sum_of_c_val=Sum('c.val'))\
                       .select_related('a')
   b_by_a = [
            {
               'grouper': key,
               'list': list(val),
               'total': sum([b.sum_of_c_val for b in val])
            }
            for key, val in
            groupby(bs, lambda b: b.a)
   ]

   return render_to_response('tmpl.html', {'b_by_a': b_by_a})

And template:

{% for b_group in b_by_a %}
  {% for b in b_group.list %}
    <tr>
        <td>{{b_group.grouper.name}}</td>
        <td>{{b.name}}</td>
        <td>{{b.sum_of_c_val}}</td>
    </tr>
  {% endfor %}
  <tr>
      <td>{{b_group.grouper.name}}</td>
      <td>Total</td>
      <td>{{b_group.total}}</td>
  </tr>
{% endfor %}

EDIT:

For compatible with Django 1.0 you have to replace annotate call with extra select or in-python amount calculation.

1. With subquery:

   bs = B.objects.all().extra(
       select={
         'sum_of_c_val': 'SELECT SUM(app_c.val) FROM app_c WHERE app_c.b_id=app_b.id'
       }).select_related('a')
   #...
   

   Where app - application name

2. This in-python calculation:

    bs = B.objects.all().select_related('a')
    for b in bs:
        b.sum_of_c_val = sum(map(int, b.c.all().values_list("val", flat=True)))
    #...
   

   But it produces N additional queries (where N = len(bs))