Add Support for multiples data fields

[dw-liedji]

I have used the library and It just works fine with one data field ("mark1" in my case). However It fail for multiples data fields. For examples I would like to display mark1, mark2 and mark3 for each student and for each course. This is my code:

from django.db.models import Sum, F

# Defining the queryset (student_marks) and other parameters

rows = "enrolled_student__student__user__username"
column = "teaching_course__course__name"

# Creating separate aggregation expressions for mark1, mark2, and mark3
data_mark1 = Sum(F('mark1'))
data_mark2 = Sum(F('mark2'))
data_mark3 = Sum(F('mark3'))

# Use the pivot function with multiple data columns
result = pivot(
    queryset=student_marks,
    rows=rows,
    column=column, 
    data={
        'mark1': data_mark1,
        'mark2': data_mark2,
        'mark3': data_mark3,
    },)

[martsberger]

Can you clarify your data model, does a single student for a single course have multiple values for mark1? I think if you showed the relevant parts of your models, I would better understand.

[dw-liedji]

Hello @martsberger

Thank you for your quick response.

The model below this text is the part of my database for managing marking for each student and teaching course. I want to report the mark1, mark2, mark3,..., markn of students in a table for every courses. The y axis contains the student name and the x axis contains all the teaching courses.

For each student and each teaching course, the mark1, mark2, mark3,...,markn must be displayed. The marks must be displayed in the subcolumns or dictionary of each teaching course. If a student does not have marks for a teaching course put "N/A" or None.

All the data must be prepared in the backend and the template just for rendering without any conditions. Don't use loops for database queries.

My model

class TeachingCourseStudentMark(BaseModel):
    organization = models.ForeignKey(
        Organization,
        on_delete=models.CASCADE,
        related_name="teaching_course_student_marks",
    )
    teaching_course = models.ForeignKey(
        TeachingCourse,
        on_delete=models.CASCADE,
        related_name="teaching_course_student_marks",
    )
    enrolled_student = models.ForeignKey(
        EnrolledStudent,
        on_delete=models.CASCADE,
        related_name="teaching_course_student_marks",
    )
    mark1 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    mark2 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    mark3 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    mark4 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    mark5 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    mark6 = models.DecimalField(
        max_digits=4, decimal_places=2, null=True, blank=True
    )
    objects = (
        managers.TeachingCourseStudentMarkManager()
    )  # Assign the custom manager

    class Meta:
        unique_together = (
            "organization",
            "teaching_course",
            "enrolled_student",
        )

        constraints = [
            models.CheckConstraint(
                check=models.Q(mark1__gte=0) & models.Q(mark1__lte=100),
                name="check_mark1_range",
            ),
            # Add similar CheckConstraint for other mark fields
            models.CheckConstraint(
                check=models.Q(teaching_course__isnull=False),
                name="check_teaching_course_not_null",
            ),
            models.CheckConstraint(
                check=models.Q(enrolled_student__isnull=False),
                name="check_enrolled_student_not_null",
            ),
            # Add more constraints as needed
        ]

    def __str__(self):
        return f"{self.enrolled_student.student.user.username} - {self.teaching_course.course.name}"

Solution tried

One of the simplest and optimal solution was to use django-pivot. This works fine if I included only one data field (only mark1, or only mark2, ..., or only markn)

This is the implementation that I expect:

from django.db.models import Sum, F

Defining the queryset (student_marks) and other parameters

rows = "enrolled_student__student__user__username"
column = "teaching_course__course__name"

Creating separate aggregation expressions for mark1, mark2, and mark3

data_mark1 = Sum(F('mark1'))
data_mark2 = Sum(F('mark2'))
data_mark3 = Sum(F('mark3'))

Use the pivot function with multiple data columns

result = pivot(
    queryset=student_marks,
    rows=rows,
    column=column, 
    data={
        'mark1': data_mark1,
        'mark2': data_mark2,
        'mark3': data_mark3,
    },)

By adding this feature the library would be more flexible.