Django: Filter in multiple models linked via ForeignKey?

Question

I'd like to create a filter-sort mixin for following values and models:

class Course(models.Model):
    title = models.CharField(max_length=70)
    description = models.TextField()
    max_students = models.IntegerField()
    min_students = models.IntegerField()
    is_live = models.BooleanField(default=False)
    is_deleted = models.BooleanField(default=False)
    teacher = models.ForeignKey(User)

class Session(models.Model):
    course = models.ForeignKey(Course)
    title = models.CharField(max_length=50)
    description = models.TextField(max_length=1000, default='')
    date_from = models.DateField()
    date_to = models.DateField()
    time_from = models.TimeField()
    time_to = models.TimeField()

class CourseSignup(models.Model):
    course = models.ForeignKey(Course)
    student = models.ForeignKey(User)
    enrollment_date = models.DateTimeField(auto_now=True)

class TeacherRating(models.Model):
    course = models.ForeignKey(Course)
    teacher = models.ForeignKey(User)
    rated_by = models.ForeignKey(User)
    rating = models.IntegerField(default=0)
    comment = models.CharField(max_length=300, default='')

• A Course could be 'Discrete mathematics 1'
• Session are individual classes related to a Course (e.g. 1. Introduction, 2. Chapter I, 3 Final Exam etc.) combined with a date/time
• CourseSignup is the "enrollment" of a student
• TeacherRating keeps track of a student's rating for a teacher (after course completion)

I'd like to implement following functions

• Sort (asc, desc) by Date (earliest Session.date_from), Course.Name
• Filter by: Date (earliest Session.date_from and last Session.date_to), Average TeacherRating (e.g. minimum value = 3), CourseSignups (e.g. minimum 5 users signed up) (these options are passed via a GET parameters, e.g. sort=date_ascending&f_min_date=10.10.12&...)

How would you create a function for that?

I've tried using

• denormalization (just added a field to Course for the required filter/sort criterias and updated it whenever changes happened), but I'm not very satisfied with it (e.g. needs lots of update after each TeacherRating).
• ForeignKey Queries (Course.objects.filter(session__date_from=xxx)), but I might run into performance issues later on..

Thanks for any tipp!

Answer 1

In addition to using the Q object for advanced AND/OR queries, get familiar with reverse lookups.

When Django creates reverse lookups for foreign key relationships. In your case you can get all Sessions belonging to a Course, one of two ways, each of which can be filtered.

c = Course.objects.get(id=1)
sessions = Session.objects.filter(course__id=c.id) # First way, forward lookup.
sessions = c.session_set.all() # Second way using the reverse lookup session_set added to Course object.

You'll also want to familiarize with annotate() and aggregate(), these allow you you to calculate fields and order/filter on the results. For example, Count, Sum, Avg, Min, Max, etc.

courses_with_at_least_five_students = Course.objects.annotate(
    num_students=Count('coursesignup_set__all')
).order_by(
    '-num_students'
).filter(
    num_students__gte=5
)


course_earliest_session_within_last_240_days_with_avg_teacher_rating_below_4 = Course.objects.annotate(
    min_session_date_from = Min('session_set__all')
).annotate(
    avg_teacher_rating = Avg('teacherrating_set__all')
).order_by(
    'min_session_date_from',
    '-avg_teacher_rating'
).filter(
    min_session_date_from__gte=datetime.now() - datetime.timedelta(days=240)
    avg_teacher_rating__lte=4
)

The Q is used to allow you to make logical AND and logical OR in the queries.