CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
Joseph jun. Melettukunnel
Joseph jun. Melettukunnel

Reputation: 6397

Django: Filter in multiple models linked via ForeignKey?

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='')

I'd like to implement following functions

How would you create a function for that?

I've tried using

Thanks for any tipp!

Upvotes: 7

Views: 3083

Answers (2)

Furbeenator
Furbeenator

Reputation: 8285

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.

Upvotes: 10

Teisman
Teisman

Reputation: 1358

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?

Upvotes: 4

Related Questions