CODEWITHSUNDEEP
pythondjangodjango-orm
gtalarico
gtalarico

Reputation: 4689

Apply filter to nested reverse foreign key relationship using queryset

Context

I am trying to filter a list of objects based on the value of a reverse foreign key attribute. I was able to solve it at the view level but, but other attempts to solve using ORM feature result in additional queries.

The outcome I want to is queryset with all objects, but related fkey objects are filtered inside each object.

Sample Models

class Student(models.Model):
    name = models.CharField(max_length=128)


class Subject(models.Model):
    title = models.CharField(max_length=128)

class Grade(models.Model):
    student = models.ForeignKey("Student", related_name="grades", on_delete=models.CASCADE)
    subject = models.ForeignKey("Subject", related_name="grades", on_delete=models.CASCADE)
    value = models.IntegerField()

Given the Fixtures

+------+------------------------+
| name | subject  | grade_value |
+------+----------+-------------+
| beth | math     | 100         |
| beth | history  | 100         |
| beth | science  | 100         |
| mark | math     | 90          |
| mark | history  | 90          |
| mark | science  | 90          |
| mike | math     | 90          |
| mike | history  | 80          |
| mike | science  | 80          |
+------+----------+-------------+

Desired Outcome

I want to render a list of students, but only include math and history grades.

For Example, maybe I want a list of students, but only include a subset of their grades:

GET students/?subjects=math,history

Which are filtered might be provided in the request, or hard coded. If possible we can leave this outside the scope of this question, and assume the filtering parameters are fixed to math and history.

{
    "students": [
        {
          "name": "beth",
          "grades": [
              {"subject": "math", "grade": 100 },
              {"subject": "history", "grade": 100 },
              // Exclude one or more grades - eg.
              // science grade not included
          ]
        },
        ...
    ]
}

Attempted Solution

Simple Filter

Just filtering. I guess this filters all students which have a grade with subjects in list, which is all.

queryset = Students.objects.all()\
   .prefetch_related("grades")\
   .filter(grades__subject__in=["math", "history"])
)
# all grades for each student eg.
...
"grades": [
  {"subject": "math", "grade": 100 },
  {"subject": "history", "grade": 100 },
  {"subject": "science", "grade": 100 },
]
...

Subquery

I don't have a great grasp on how subqueries work, but using some examples I had I tried:

subjects = Subject.objects.filter(
    name__in=["math", "history"]
) 
queryset = Students.objects.all()\
    .prefetch_related("grades")\
    .filter(grades__subject__name__in=Subquery(subjects.values("name")))

And another variation:

grades = Grades.objects.filter(
    student_id=OuterRef("id"), subject__name__in=["math", "history"]
) 
queryset = Students.objects.all()\
     .prefetch_related("grades")\
     .filter(grades__pk__in=Subquery(grades.values("pk)))

Both returned students with all grades.

Workaround Solution

This solutions filters grades using python. It works but I would rather get this working with querysets

# in view:

serializer = StundentSerializer(queryset, many=True)
response_data = serializer.data

for student in response_data:
   student.grades = [g for g in students.grades if g["subject"] in ["math", "history"]]

...
# return response_data

Upvotes: 2

Views: 933

Answers (1)

Arsh Singh
Arsh Singh

Reputation: 2116

You can use the Prefetch object: https://docs.djangoproject.com/en/3.0/ref/models/querysets/#django.db.models.Prefetch

For e.g.:

qs = Students.objects.all().prefetch_related(
    Prefetch('grades', queryset=Grade.objects.filter(subject__title__in=["math", "history"])
)

qs[0].grades.all() will now only have math and history grades.

Optionally you can provide the to_attr='math_history_grades' argument to Prefetch, so then you'll access the grades by: qs[0].math_history_grades.all()

Upvotes: 3

Related Questions