Django QuerySet使用order_by排序时Annual数据重复返回三次

Annual模型查询集使用order_by排序拉取年度报告时，同一份数据会重复返回3次；移除order_by参数后数据返回完全正常，目前仅能通过不使用排序临时规避问题。

相关代码 #

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)

models.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=DO_NOTHING, blank=True)
    subject = models.ForeignKey(Subject, on_delete=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.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)

重复返回数据是跨多对多关联排序触发的表连接导致的：Group模型和Students是多对多关系，当使用group__student__level作为排序字段时，ORM会自动生成LEFT JOIN语句关联Group-Student中间表、学生表。如果一个Group下绑定了3个学生，同一条Annual记录就会在连接后的结果集中出现3次，最终被ORM映射为3条重复的Annual对象。

• 方案1：追加distinct()去重
  在查询集末尾加.distinct()，按Annual模型主键去重，直接过滤多对多表连接产生的重复行：

  queryset = Annual.objects.all().order_by('-group__student__level', '-group__gName').distinct()
  

  注意：PostgreSQL数据库支持给distinct()传参指定去重字段，MySQL、SQLite等数据库直接不传参即可，默认按模型主键去重。
• 方案2：调整排序逻辑规避跨多对多表关联
  如果业务允许，尽量不跨多对多字段排序，从根源上避免多表连接产生重复行。比如仅按组名排序时，直接使用group__gName字段即可，不需要关联多对多的student表。

内容的提问来源于stack exchange，提问作者Sahil Mohile