problems with duplicates in querysets

atomas1 · June 15, 2023, 10:25pm

I’m working on project of electronic diary in django, I’ve found a problem with querysets when I filled the database with more than one students. for example i have duplicated rows in this view:

my models.py

class Teacher(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    phone = models.CharField(max_length=24)

    class Degree(models.IntegerChoices):
        MASTER = -1
        DOCTOR = 0
        PROFESSOR = 1
        OTHER = 2

    degree = models.IntegerField(choices=Degree.choices, default=-1)
    items = models.ManyToManyField(Item, through='TeacherItemYear', related_name='teachers')

    def __str__(self):
        return f"{self.user.first_name} {self.user.last_name}"


class TeacherItemYear(models.Model):
    teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    academic_year = models.ForeignKey(AcademicYear, on_delete=models.CASCADE)

    class Meta:
        unique_together = ('teacher', 'item', 'academic_year')

    def __str__(self):
        return f"{self.academic_year} - {self.teacher} - {self.item}"

class Rating(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE, related_name='ratings')
    teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE, related_name='ratings')
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='ratings')
    date = models.DateField()
    value = models.DecimalField(max_digits=3, decimal_places=2)
    notice = models.TextField()

    def __str__(self):
        return f"{self.student} - {self.item} - {self.date}: {self.value}"


class Lesson(models.Model):
    localization = models.ForeignKey('Localization', on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='lessons')
    level = models.ForeignKey(Level, on_delete=models.CASCADE, related_name='lessons')
    reunion = models.ForeignKey('Reunion', on_delete=models.CASCADE, related_name='lessons')
    present_students = models.ManyToManyField(Student, related_name='lessons')
    topic = models.CharField(max_length=128)
    date_time = models.DateTimeField()
    references = models.FileField(upload_to="student", blank=True)
    teacher = models.ForeignKey(Teacher, on_delete=models.CASCADE, related_name='lessons')

    def __str__(self):
        return f"{self.item} - {self.topic}"


class Reunion(models.Model):
    academic_year = models.ForeignKey(AcademicYear, on_delete=models.CASCADE, related_name="reunions")
    level = models.ForeignKey(Level, on_delete=models.CASCADE, related_name='reunions')
    start_at = models.DateField()
    finish_at = models.DateField()

    def __str__(self):
        return f"{self.academic_year} {self.level}: {self.start_at} - {self.finish_at}"


class Localization(models.Model):
    name = models.CharField(max_length=128)
    address = models.TextField()

    def __str__(self):
        return f"{self.name}"


class Exam(models.Model):
    academic_year = models.ForeignKey(AcademicYear, on_delete=models.CASCADE)
    localization = models.ForeignKey(Localization, on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE, related_name='exams')
    date_time = models.DateTimeField()
    students = models.ManyToManyField(Student, through='StudentExam', related_name='exams')

    def __str__(self):
        return f"{self.academic_year} - {self.item}"


class StudentExam(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    exam = models.ForeignKey(Exam, on_delete=models.CASCADE)
    rating = models.DecimalField(max_digits=3, decimal_places=2, null=True)
    is_passed = models.BooleanField(default=False)

    class Meta:
        unique_together = ('student', 'exam',)

    def __str__(self):
        return f"{self.student} - {self.exam}: {self.rating}"

class Student(models.Model):
    class Confession(models.IntegerChoices):
        NOT_DEFINED = -1
        ROMAN_CATHOLIC = 0
        GREEK_CATHOLIC = 1
        ORTHODOX = 2
        OTHER = 3

    class Education(models.IntegerChoices):
        HIGHER = -1
        SECONDARY = 0
        VOCATIONAL = 1
        PRIMARY = 2
        OTHER = 3

    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    academic_years = models.ManyToManyField('AcademicYear', related_name='students')
    phone = models.CharField(max_length=24)
    birth_date = models.DateField()
    confession = models.IntegerField(choices=Confession.choices, default=-1)
    education = models.IntegerField(choices=Education.choices, default=-1)
    address = models.TextField(blank=True)
    workplace = models.TextField(blank=True)
    comments = models.TextField(blank=True);
    index = models.CharField(max_length=24, blank=True, unique=True)
    diploma = models.CharField(max_length=24, blank=True, unique=True)
    level = models.ForeignKey('Level', related_name='students', on_delete=models.CASCADE)

    def __str__(self):
        return f"{self.user} - level {self.level}"


class AcademicYear(models.Model):
    name = models.CharField(max_length=12)
    started_at = models.DateField()
    finished_at = models.DateField()
    items = models.ManyToManyField('Item', related_name='academicyears', through='StudentItemYear')

    def __str__(self):
        return f'{self.name}'


class Level(models.Model):
    class LevelNumber(models.IntegerChoices):
        FIRST = 1
        SECOND = 2
        LAST = 3

    level_number = models.IntegerField(choices=LevelNumber.choices, default=1)

    def __str__(self):
        return f"{self.level_number}"


class Item(models.Model):
    name = models.CharField(max_length=128)
    students = models.ManyToManyField(Student, through='StudentItemYear', related_name='items')

    def __str__(self):
        return f"{self.name}"


class StudentItemYear(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    academic_year = models.ForeignKey(AcademicYear, on_delete=models.CASCADE)

    class Meta:
        unique_together = ('student', 'item', 'academic_year',)

    def __str__(self):
        return self.student.user.first_name + " " + self.student.user.last_name + " " + " " + self.item.name + " " + self.academic_year.name

my view:

class Home(LoginRequiredMixin, View):
    def get(self, request):
        is_teacher = self.request.session.get('is_teacher')
        current_academic_year = AcademicYear.objects.filter(
            name=self.request.session.get('current_academic_year')).first()
        items = Item.objects.filter(
            Q(teachers__in=[self.request.user.teacher]) & Q(academicyears__in=[current_academic_year])).order_by(
            'name')
        students = []
        for item in items:
            for student in item.students.filter(academic_years__in=[current_academic_year]).order_by(
                    'user__last_name'):
                student.item = item
                student.item.not_present = item.lessons.filter(
                    reunion__academic_year=current_academic_year).count() - Lesson.objects.filter(item=item).filter(
                    present_students__in=[student]).count()
                students.append(student)
        ratings = Rating.objects.filter(item__in=items).filter(teacher=request.user.teacher)
        return render(request, 'teacher/home.html',
                      context={'items': items, 'students': students, 'is_teacher': is_teacher, 'ratings': ratings})

my template:

{% for item in items %}
        <table class="table table-sm">

            <thead class="thead-dark">
            <tr>
                <th scope="col" colspan="4">{{ item.name }}</th>
            </tr>

            <tr>
                <th>FIRST NAME</th>
                <th colspan="2">SECOND NAME</th>
                <th>NOT PRESENT</th>
            </tr>
            </thead>
            <tbody>

            {% for student in students %}
                {% if student.item.pk == item.pk %}
                    <tr class="table-warning">
                        <td>{{ student.user.first_name }}</td>
                        <td colspan="2">{{ student.user.last_name }}</td>
                        <td>{{ student.item.not_present }}</td>
                    </tr>
                {% endif %}
                <tr>
                    <th colspan="4">RATINGS</th>
                </tr>
                <tr>
                    <th>DATE</th>
                    <th>VALUE</th>
                    <th>NOTICE</th>
                    <th></th>
                </tr>
                {% for rating in ratings|for_student:student %}
                    <tr>
                        <td>{{ rating.date }}</td>
                        <td>{{ rating.value }}</td>
                        <td>{{ rating.notice }}</td>
                        <td><a href="{% url 'teacher:rating-update'  pk=rating.id %}">
                            <button class="btn btn-danger btn-sm">EDIT</button>
                        </a></td>
                    </tr>
                {% endfor %}
            {% endfor %}
            </tbody>
        </table>

    {% endfor %}

and I have duplicated items and students in view. I think using distinct() is not enough…

Rigo-Villalta · June 16, 2023, 10:46pm

I don’t understand how a teacher is related to a Item in your models. So how your queryset is what you have.

Topic		Replies	Views
Duplicate Queries from a formset Using the ORM	2	58	July 28, 2024
Django ORM Group by issue Using Django	3	959	August 3, 2021
Case When Query return duplicated objects ORM	7	2407	August 31, 2022
Lesson learned--Check your queries' SQL Using Django	1	492	July 14, 2021
SearchQuery and duplicate in queryset Using the ORM	4	4836	April 13, 2022

problems with duplicates in querysets

Related topics