CODEWITHSUNDEEP
pythonmysqldjangopython-2.7prefetch
Ankit Singh
Ankit Singh

Reputation: 273

Django - Filter the prefetch_related queryset

I am trying to reduce my complexity by doing the following. I am trying to get all the teachers in active classrooms.

teacher/models.py:

Teacher(models.Model):
    name = models.CharField(max_length=300)


classroom/models.py:

Classroom(models.Model):
    name = models.CharField(max_length=300)
    teacher = models.ForeignKey(Teacher)
    students = models.ManyToManyField(Student)
    status = models.CharField(max_length=50)

admin/views.py

teachers = Teacher.objects.prefetch_related(Prefetch('classroom_set',queryset=Classroom.objects.filter(status='Active'))


for teacher in teachers:
    classrooms = teacher.all()
    # run functions

By doing this I get teachers with classrooms. But it also returns teachers with no active classrooms(empty list) which I don't want. Because of this, I have to loop around thousands of teachers with empty classroom_set. Is there any way I can remove those teachers whose classroom_set is [ ]?

This is my original question - Django multiple queries with foreign keys

Thanks

Upvotes: 9

Views: 13234

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477686

If you want all teachers with at least one related active class, you do not need to prefetch these, you can filter on the related objects, like:

Teacher.objects.filter(class__status='Active').distinct()

If you want to filter the classroom_set as well, you need to combine filtering as well as .prefetch_related:

from django.db.models import Prefetch

Teacher.objects.filter(
    class__status='Active'
).prefetch_related(
    Prefetch('class_set', queryset=Class.objects.filter(status='Active'))
).distinct()

Here we thus will filter like:

SELECT DISTINCT teacher.*
FROM teacher
JOIN class on class.teacher_id = teacher.id
WHERE class.status = 'Active'

Upvotes: 15

Related Questions