Django: Generate averages for ratings for a review site with multiple rating critera

Question

Working on a project in Python(3.8) and Django, trying to implement a review system that has multiple criteria.

Note: I've searched for awhile but haven't read anything that specifically solves my issue.

I have 3 models:

from django.db import models
from django.conf import settings
from django.urls import reverse


# Create your models here.
class Board(models.Model):
    name = models.CharField(max_length=30, unique=True)
    description = models.CharField(max_length=100)

    def __str__(self):
        return self.name

    def get_reviews_count(self):
        return Review.objects.filter(company__board=self) .count()

    def get_last_review(self):
        return Review.objects.filter(company__board=self) .order_by('created_at') .first()

class Company(models.Model):
    name = models.CharField(max_length=255, unique=True)
    #bio = models.TextField(max_length=4000)
    last_updated = models.DateTimeField(auto_now_add=True)
    board = models.ForeignKey(Board, on_delete = models.CASCADE, related_name='companies')
    starter = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name='companies',
    )
    views = models.PositiveIntegerField(default=0)

    def __str__(self):
        return self.name

    def get_absolute_url(self):
        return reverse('company_detail', args=[str(self.id)])

    def get_recent_reviews(self):
        return self.reviews.order_by('-created_at')

    def get_last_ten_reviews(self):
        return self.reviews.order_by('-created_at')[:10]

class Review(models.Model):
    RATING_CHOICES = (
        ('1', '1'),
        ('2', '2'),
        ('3', '3'),
        ('4', '4'),
        ('5', '5'),
    )
    STAY = (
        ('less than 6 months', 'less than 6 months'),
        ('6 months', '6 months'),
        ('10 months', '10 months'),
        ('12 months', '12 months'),
        ('More than 1 year', 'More than 1 year'),
    )
    YES_NO = (
        ('Yes', 'Yes'),
        ('No', 'No'),
    )
    SECURITY = (
        ('100%', '100%'),
        ('75%', '75%'),
        ('50%', '50%'),
        ('25%', '25%'),
        ('0%', '0%'),
        ('Still waiting', 'Still waiting'),
    )

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(null=True)
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete = models.CASCADE, related_name='reviews')
    updated_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete = models.CASCADE, null=True, related_name='+')
    company = models.ForeignKey(Company, on_delete = models.CASCADE, related_name='reviews')
    address = models.CharField(max_length=200, blank=False, default="")
    length_of_stay = models.CharField(max_length=20, choices=STAY, blank=False)
    #Apartment Condition
    move_in_condition = models.CharField(max_length=5,choices=RATING_CHOICES)
    #Landlord Interaction
    treatment = models.CharField(max_length=5, choices=RATING_CHOICES, blank=False, default ="3")
    response_speed = models.CharField(max_length=5, choices=RATING_CHOICES, blank=False, default ="3")
    maintenance_quality = models.CharField(max_length=5, choices=RATING_CHOICES, blank=False, default ="3")

    security_deposit_returned = models.CharField(max_length=5, choices=SECURITY, blank=False, default ="1")
    #put text "ignore if still waiting"
    is_this_a_fair_amount = models.CharField(max_length=5, choices=YES_NO, blank=False, default="1")
    would_you_recommend = models.CharField(max_length=5, choices=YES_NO, blank=False, default="1")
    additional_comments = models.TextField(max_length=4000)

    def __str__(self):
        return self.address

Views.py

def board_companies(request, pk):
    board = get_object_or_404(Board, pk=pk)
    companies = board.companies.order_by('-last_updated') .annotate(replies=Count('reviews'))
    return render(request, 'companies.html', {'board': board, 'companies': companies})

def company_reviews(request, pk, company_pk):
    company = get_object_or_404(Company, board__pk=pk, pk=company_pk)
    company.views += 1
    company.save()
    return render(request, 'company_reviews.html', {'company': company})

The user posts a review about a company and inside the review form there are many criteria.

I would like to average some of the criteria together to get a overall rating for the review post, which will be displayed with each review post.

Then average all the overall ratings of the company, and display that on the company's review page, and the company list page.

Then find the total average of each individual criteria by itself and show it on the company review page underneath the overall rating (average for treatment, average for response_speed, etc)

Thank you in advance!

Answer 1

Try using F expressions and combining aggregates with annotations:

# Annotate with average of multiple rating columns, 
# then aggregate all overall ratings and each individual rating of all reviews
Company.objects.annotate(overall_rating = Avg(
    F('move_in_condition') + 
    F('treatment') + 
    F('response_speed') +
    F('maintenance_quality') / 4
)).aggregate(
    Avg('overall_rating'), 
    Avg('move_in_condition'),
    Avg('treatment'),
    Avg('response_speed'),
    Avg('maintenance_quality')
)

This entire statement performs all three of the things you specified. You may need to specify output_field argument in Avg functions in case you get an error

Havent' tested this, but I based myself straight from docs: https://docs.djangoproject.com/en/2.2/ref/models/expressions/#f-expressions https://docs.djangoproject.com/en/2.2/topics/db/aggregation/#aggregating-annotations