Django - Counting filtered model instances per row in a template table

Question

Let's say I have the following django models:

class House(Model):
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')
    rooms = IntegerField(null=True)

class Room(Model): 
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')
    house = CharField(max_length=200, default='')
    furniture = IntegerField(null=True)

class Furniture(model): 
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')
    room = CharField(max_length=200, default='')

And I want to generate the following table in a template:

Room	In house	Amount of furniture
Living Room	Summer house	5
Kitchen	Summer house	8
Bedroom	Summer house	2
Bathroom	Main house	3

Where the column "Amount of furniture" counts the instances of the "furniture" model where the field "room" is equal to that of the entry in the column "room" for that row.

I'm trying to figure out how to do this, and I've landed on a few different ways - ranked from most ideal/pythonic to least ideal/pythonic.

1. Building some sort of mechanism into the model. This would be perfect, but I can't seem to find any obvious way to do it.
2. Adding a function that generates a dictionary in the view in views.py. Would be easy to build (gather names of "room", make a for loop doing a filter query for each room on the model "furniture", add a counter variable, and build a dictionary) but not very flexible or pythonic.
3. Using a third party module like datatables. Feels like overkill - but may be a better long term solution?
4. Setting up some real shenaningans in the template language. Since you can't declare variables in the template, i imagine this would be a spider web of nested loops, conditionals, and custom template tags.

Which approach should I go for here?

Answer 1

You should work a ForeignKey [Django-doc] to link models. This is part of database normalization [wiki] to prevent data duplication and making databases more manageable:

class House(Model):
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')

class Room(Model): 
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')
    house = ForeignKey(House, on_delete=CASCADE)

class Furniture(model): 
    author = ForeignKey(settings.AUTH_USER_MODEL, on_delete=CASCADE)
    title = CharField(max_length=200, default='')
    text = TextField(default='')
    room = ForeignKey(Room, on_delete=CASCADE)

there is also no need to store the number of Rooms or Furniture: you can determine that when necessary. In your view you can query the Room model with:

from app_name.models import Room
from django.db.models import Count

def some_view(request):
    rooms = Room.objects.select_related('house').annotate(
        num_furniture=Count('furniture')
    )
    return render(request, 'app_name/some_template.html', {'rooms': rooms})

here we thus annotate the Rooms with the number of related Funiture with Count('furniture'). The database will simply count the number of Furnitures per Room: this is more robust since it does not require logic when creating, updating, removing a Furniture, Room, etc.

and in the template, you then can render the table with:

<table>
  <thead>
    <tr><th>Room</th><th>In house</th><th>Amount of furniture</th></tr>
  </thead>
  <tbody>
  {% for room in rooms %}
    <tr><td>{{ room.title }}</td><td>{{ room.house.title }}</td><td>{{ room.num_furniture }}</td></tr>
  {% endfor %}
  </tbody>
</table>