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!

Furbeenator · Accepted Answer · 2012-11-29 01:21:00Z

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.

Teisman · Accepted Answer · 2012-11-23 18:01:03Z

4

I recommend you take a look at complex lookups: https://docs.djangoproject.com/en/1.5/topics/db/queries/#complex-lookups-with-q-objects

The following query might not work in your case (what does the teacher model look like?), but I hope it serves as an indication of how to use the complex lookup.

from django.db.models import Q

Course.objects.filter(Q(session__date__range=(start,end)) &
                      Q(teacher__rating__gt=3))

Unless absolutely necessary I'd indeed steer away from denormalization.

Your sort question wasn't entirely clear to me. Would you like to display Courses, filtered by date_from, and sort it by Date, Name?

answered Nov 23, 2012 at 18:01

Teisman

1,3682 gold badges13 silver badges26 bronze badges

1 Comment

Joseph jun. Melettukunnel Over a year ago

Thanks for the update, mark! I forgot to mention the case when I'd like to check e.g. how many students have signed for a course and filter for that. Are Q Objects still the way to go? How about performance (compared to denormalization)?

Collectives™ on Stack Overflow

Django: Filter in multiple models linked via ForeignKey?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related