Django Order_by Retrieves same data thrice-CodePudding

I have a model called as Annual. When I am retrieving the annual report from database after using order_by on queryset same data is retrieved thrice.

Here's my views.py

class AnnualView(APIView):
    serializer_class = AnnualSerializer

    def get(self, request):
        queryset = Annual.objects.all().order_by('-group__student__level', '-group__gName')
        print(queryset)
        serializer = self.serializer_class(queryset, many=True)
        for serialized_data in serializer.data:
            current = serialized_data.get('current')
            for data in current:
                data.get('topic')['current']= data.get('current')
                data.get('category')['topic']= data.get('topic')
            
                data.pop('topic')
                data.pop('current')
        
        context = {
            "Status": 200,
            "payload": serializer.data
        }

        return Response(context)

Here's my Model.py

class Group(models.Model):
    id = models.AutoField(primary_key = True)
    student = models.ManyToManyField(Students, related_name="student_groups", default="No Student")
    gName = models.CharField(max_length=10)
    teacher = models.ForeignKey(User, related_name="GroupTeacher", on_delete=DO_NOTHING)
    speciality = models.ForeignKey(SubjectSpeciality, on_delete=models.DO_NOTHING, blank=True)
    subject = models.ForeignKey(Subject, on_delete=models.DO_NOTHING, blank=True)

class AnnualCurrentPL(models.Model):
    id = models.AutoField(primary_key = True)
    category = models.ForeignKey(Category, on_delete=DO_NOTHING)
    topic = models.ForeignKey(Topic, on_delete=DO_NOTHING)
    current = models.CharField(max_length=512)
    annual = models.ForeignKey("Annual", on_delete=DO_NOTHING, blank=True, null=True)

# Annual Report Model
class Annual(models.Model):
    id = models.AutoField(primary_key=True)
    teacher = models.CharField(max_length=100)
    group = models.ForeignKey(Group, on_delete=models.CASCADE)
    year = models.CharField(max_length=100, default="2022")
    # current = models.CharField(max_length=512, default="current")
    current = models.ManyToManyField(AnnualCurrentPL, related_name="AnnualCurrentPL", blank=True)
    expectations = models.CharField(max_length=512, blank=True)
    month = models.ManyToManyField("MonthAnnual", blank=True)

class MonthAnnual(models.Model):
    id = models.AutoField(primary_key=True)
    numeric_Month = models.IntegerField()
    month = models.CharField(max_length=100)
    year = models.IntegerField()
    teacher = models.CharField(max_length=255)
    goal = models.CharField(max_length=512)

I have included all models responsible for end result.

When I disable the order_by from queryset in views.py data is retrieved normally. Have spend hours to figure it out. Only solution I found is to not include order_by in function.

CodePudding user response：

How about adding .distinct()? Will that solve the issue?

CodePudding user response：

Your order_by is causing an implicit join on many tables, including at least one M2M relationship. Your ordering will result at the very least in an entry per student/group/annual relationship. If you do a print(queryset.values("id", "group__student__level", "group__gName")), you'll be able to observe exactly what's happening. You must either use .distinct() to grab only the first annual for each entry, or some sort of .aggregate(). The official documentation has good examples of both.

And you can always use queryset.query to see what the SQL looks like in the future, which is a good starting place for debugging your queries.