Create query between ManyToMany relationship models in Django ORM

Question

class Review(models.Model):
    student = models.ForeignKey(UserDetail)
    text = models.TextField()
    created_at = models.DateTimeField(auto_now=True)
    vote_content = models.FloatField()
    vote_knowledge = models.FloatField()
    vote_assignment = models.FloatField()
    vote_classroom = models.FloatField()
    vote_instructor = models.FloatField()

class UserDetail(models.Model):
    username = models.CharField(max_length=100)
    email = models.CharField(max_length=255)
    ...

class Course(models.Model):
    title = models.CharField(max_length=255)
    studentlist = models.ManyToManyField(UserDetail, related_name='course_studentlist', blank=True)
    reviewlist = models.ManyToManyField(Review,related_name='course_reviewlist', blank=True)
    ...

In the above model structure, the Course model has a relationship with UserDetail and Review with ManyToMany.

The review is based on the average of the 5 votes. (content, knowledge etc.)

A review of a course is the average of the votes of the students who take the course.

I would like to make a search and sort according to Course's review, for example, list bigger than 3 votes.

Thanks for your help.

Answer 1

The easiest and probably the cleanest solution is to create additional field for storing average score in Review and calculate it on save().

Is there a reason why you keep course reviews as m2m field? Do you allow the same review, with the same text etc. to be used in many courses? Maybe you need ForeignKey in this case. Then you could just do:

class Review(models.Model):
    ...
    vote_avg = models.FloatField()
    course = models.ForeignKey('Course')
    ...

    def save(self, *args, **kwargs):
        self.voce_avg = (self.vote_content + ...) / 5
        super(Review, self).save(*args, **kwargs)


def foo():
    return Course.objects.prefetch_related('review_set').annotate(
        avg_reviews=Avg('review__vote_avg')
    ).filter(avg_reviews__gt=3).order_by('avg_reviews')