How can I iterate through an SQL database using a function within a Django model?

Question

I need to know how I can create a function within a Django model which iterates through a SQL database, specifically another model.

My Django project contains two models, 'Accelerator' and 'Review'. The Accelerator model has a decimal field 'overall_rating', the value of which is to depend on the accumulation of values inputted into one of the Review model fields, 'overall'. To make this work, I have concluded that I need to create a function within the Accelerator model which:

Iterates through the Review model database
Adds the value of Review.overall to a list where a certain condition is met
Calculates the total value of the list and divides it by the list length to determine the value for overall_rating

The value of accelerator.overall_rating will be prone to change (i.e. it will need to update whenever a new review of that 'accelerator' has been published and hence a new value for review.overall added). So my questions are:

Would inserting a function into my accelerator model ensure that its value changes in accordance with the review model input?
If yes, what syntax is required to iterate through the review model contents of my database?

(I've only included the relevant model fields in the shared code)

class Accelerator(models.Model):
    overall_rating = models.DecimalField(decimal_places=2, max_digits=3)

class Review(models.Model):
    subject = models.ForeignKey(Accelerator, on_delete=models.CASCADE, blank=False)
    overall = models.DecimalField(decimal_places=2, max_digits=3)

You should not iterate over your database, but aggregate/annotate. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jul 8, 2019 at 10:36

willeM_ Van Onsem · Accepted Answer · 2019-07-25 09:29:21Z

You usually do not calculate aggregates yourself, but let the database do this. Databases are optimized to do this. Iterating over the collection would result in the fact that the database needs to communicate all the relevant records which will result in using a lot of bandwidth.

If you aim to calculate the average rating, you can just aggregate over the review_set with the Avg aggregate [Django-doc], like:

from django.db.models import Avg

class Accelerator(models.Model):

    @property
    def average_rating(self):
        return self.review_set.aggregate(
            average_rating=Avg('overall')
        )['average_rating']

The above is not very useful if you need to do this for a large set of Accelerators, since that would result in a query per Accelerator. You can however annotate(..) [Django-doc] your Accelerator class, for example with a Manager [Django-doc]:

from django.db.models import Avg

class AcceleratorManager(models.Manager):

    def get_queryset(self):
        return super().get_queryset().annotate(
            _average_rating=Avg('review__overall')
        )

Then we can alter the Accelerator class to first take a look if the value is already calcuated by the annotation:

class Accelerator(models.Model):

    objects = models.Manager()
    objects_with_rating = AcceleratorManager()

    @property
    def average_rating(self):
        try:
            return self._average_rating
        except AttributeError:
            self._average_rating = result = self.review_set.aggregate(
                average_rating=Avg('overal')
            )['average_rating']
            return result

If we then thus access for example Accelerator.objects_with_rating.filter(pk__lt=15), we will in bulk calculate the average rating of these Accelerators.

Storing the average in the database is likely not a good idea, since it introduces data duplication, and synchronizing data duplication tends to be a hard problem.

Collectives™ on Stack Overflow

How can I iterate through an SQL database using a function within a Django model?

1 Answer 1

Comments

Your Answer

Collectives™ on Stack Overflow

How can I iterate through an SQL database using a function within a Django model?

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related