Join Query in Django ORM to Create Report

Question

I have the following models structure:

class Student(models.Model):
    full_name = models.CharField(max_length=100)
    std_class = models.CharField(max_length=50)

class Teacher(models.Model):
    full_name = models.CharField(max_length=100, unique=True)

class Attendance(models.Model):
    att_date = models.DateField(default=date.today)
    teacher = models.ManyToManyField(Teacher)
    subject_name = models.CharField(max_length=50)
    std_class = models.CharField(max_length=50)
    total_students = models.IntegerField(default=0)

class Entry(models.Model):
    attendance = models.ForeignKey(Attendance, on_delete=models.CASCADE)
    student = models.ForeignKey(Student, on_delete=models.CASCADE) 

I want to build a report like below for all students in the given class:

Student Name	Attendance Date 1,Teachers Name	Attendance Date 2,Teachers Name	...
Student 1	P	A	...
Student 2	A	P	...
Student 3	P	P	...
...	...	...	...
Student N	A	A	...

The P (Present) should be marked if Entry has a row that contained attendance for a given student otherwise A (Absent).

I've built the code and it working correctly, but it is very inefficient.

report_dict = {}
all_students = Student.objects.filter(std_class=class_full_name)

for name in all_students:
    report_dict[name.full_name] = []

attendance_list = Attendance.objects.filter(Q(std_class=class_full_name) & Q(att_date__month=curr_month)).order_by('att_date')

for rec in attendance_list:
    # Make absent all student initially
    for key in report_dict:
        report_dict[key].append("A")

    # Get all attendance for current attendance id
    entries = Entry.objects.filter(attendance=rec)

    # Mark present to the selected student
    for e in entries:
        if report_dict[str(e.student)] != None:
            report_dict[str(e.student)][-1] = "P"

The column name for attendance date and teacher in report is rendered from attendance_list separately in the template:

<tr>
    <th>Student Name</th>

    {% if attendance_list %}
        {% for item in attendance_list %}
            <th>{{ item.att_date }} <br>

                {% for teacher_name in item.teacher.all %}
                    {{ teacher_name }}<br/>
                {% endfor %}

                {{ item.subject_name }}
            </th>
        {% endfor %}
    {% endif %}
</tr>

Is there a better approach to solving this problem and improving speed?

Answer 1

Query optimization:

Add prefetch_related to Attendance queryset like this:

attendance_list = Attendance.objects.prefetch_related('teacher', 'entry_set').filter(Q(std_class=class_full_name)

And then in your loop just get the object like this:

enteries = rec.entry_set.all()

Python optimization:

Use dictionary comprehension like this:

report_dict = {student.name.full_name: [] for student in all_students}

I believe the code for making students absent intially should be outside the outer loop.