Django orm left join return data like mysql

Question

I have two table like this:

class Classroom(models.Model):
    name = model.ChartField()

class Student(models.Model):
    name = model.ChartField()
    classroom = model.ForeignKey(to='Classroom', related_name='students')

class Score(models.Model):
    student = model.ForeignKey(to='Student', related_name='scores')
    score = model.IntegerField()

there has some data like this:

Classroom:

name
Classroom_A
Classroom_B
Classroom_C

Student:

name	classroom
student_1	Classroom_A
student_2	Classroom_A
student_3	Classroom_B

Score:

student	score
student_1	70
student_1	80
student_3	90

Now I want to get the data by Django orm like this (return 4 rows data. Student's score get the max score as the student score. If the line's score is null, use 0 replace):

Classroom_name	student_name	score
Classroom_A	student_1	80
Classroom_A	student_2	0
Classroom_B	student_3	90
Classroom_C	null	0

The sql maybe like this:

-- sql1
SELECT
    classroom.name AS classname_name,
    student.`name` AS student_name,
    IF (
        t.`score` IS NULL,
        0,
        t.`score`
    ) AS `score`
FROM
    classroom
    LEFT JOIN student
        ON student.classroom = classroom.name
    LEFT JOIN
        (SELECT
            student,
            MAX(score) AS score
        FROM
            score
        GROUP BY student) AS t
        ON t.student = student.name

How can I write the orm?

I will use the result do some aggregate, maybe like this:

-- sql1 is the above code
select avg(score) from (sql1)

Answer 1

I think something like that would get similar results as you want

class StudentSerializer(serializers.ModelSerializer):
    class Meta:
        model = Student
        fields = ['name',]


class ClassroomSerializer(serializers.ModelSerializer):
    students = StudentSerializer(many=True)
    class Meta:
        model = Classroom
        fields = [
            'name',
            'students',
        ]

Now you have to serialize classrooms using ClassroomSerializer and data should look like this:

[
    {
    "name":"class_a",
    "students":
         [
             {"name":"student_a"},
             {"name":"student_b"},
         ]
    },
    other_classes...
]