How to correctly represent a "warehouse" in django - n+1 problems

Question

I'm learning Django. I am trying to make an app for managin a warehouse that's composed by simple elements:

Involved models are transaction, items, warehouses and homes.

models.py:

class Home(Model):
    name = models.CharField(max_length=255, verbose_name=_('name'),blank=False)

class Warehouse(Model):
    name = models.CharField(max_length=255, verbose_name=_('name'),blank=False)

class Item(Model):
    name = models.CharField(max_length=255, verbose_name=_('name'),
    whouse = models.ForeignKey('Warehouse', blank=True,null=True,on_delete=models.CASCADE, related_name='all_items')
    quantity    = models.PositiveSmallIntegerField(verbose_name=_('Quantity'), default=1, blank=False)
    @cached_property
    def count_remaining(self):
        remaining = self.quantity
        qs = self.transactions.all()
        if qs:
            out = qs.aggregate(Sum('quantity'))['quantity__sum']
            if out:
                remaining -= out
        return remaining


class Transaction(Model):
    item_field = models.ForeignKey('Item', blank=True,null=True,on_delete=models.CASCADE, related_name='all_transactions')
    home_field = models.ForeignKey('Home', blank=True,null=True,on_delete=models.CASCADE, related_name='all_transactions')
    quantity = models.PositiveSmallIntegerField(verbose_name=_('Quantity'), default=1, blank=False)

item_list.html (for the generic ListView, with qs=Item.objects.all()):

...
<table>
    <thead>
    <tr>
        <td>name</td>
        <td>quantity</td>
        <td>remaining items</td>
        <td>whouse</td>
    </tr>
    </thead>
    <tbody>
    {% for item in item_list %}
        <td>{{ item.name }}</td>
        <td>{{ item.quantity }}</td>
        <td>{{ item.count_remaining }}</td>
        <td>{{ item.whouse }}</td>
    {% endfor %}
    </tbody>
</table>
...

Now my problem is that my count_remaining method is being called one time for each object in the queryset, obviously resulting in an exponential number of database hits when there are many items in item_list.

How can I make this arithmetical operation without hitting the db so much, provided that I must have a page that displays in the template the remaining items in the warehouse?

Answer 1

You can use exists() instead all():

@cached_property
def count_remaining(self):
    remaining = self.quantity
    # related_name='all_transactions'
    qs = self.all_transactions
    if qs.exists():
        out = qs.aggregate(Sum('quantity'))['quantity__sum']
        remaining -= out
    return remaining

See django.db.models.query.QuerySet.exists