[FIXED] How to use Django's GeneratedField to keep count of related objects?

Issue

Let's say I have the following models:

class Shop(models.Model):
    ...
    @property
    def products_count(self):
        return Product.objects.filter(shop=self).count()

class Product(models.Model):
    shop = models.ForeignKey(Shop, on_delete=models.CASCADE)
    ...

The products_count property is being used to get the number of products in a shop. With the release of Django 5 and generated fields, is there a way to use this to store the count instead? Or am I misunderstanding the use of generated fields?

Solution

Django generates an SQL expression based on the GeneratedField with GENERATED ALWAYS syntax. While it might be possible that a database would implement that for aggregates, but as far as I know, no database currently does that.

PostgreSQL for example, which usually ships with more batteries included than other databases, says in the documentation about generated columns ^[pgsql-doc]:

The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

But even if we imagine a database where that would be possible, it is likely a recipe for endless recursion. Indeed, if a generated column would depend on a different table, it would mean that an update to that table triggers an update in the first table, but that could in its turn trigger other updates, and might eventually trigger the second table. So a small update in one column could result in large amounts of updates, and more importantly, could trigger a sequence of updates that never ends.

I think in this case it might be better to just annotate the number of products when needed, so:

from django.db.models import Count

Shop.objects.annotate(products_count=Count('product'))

and omit the property, this will also prevent an N+1 problem.

Answered By - willeM_ Van Onsem