DRF ModelViewSet返回get_queryset结果时排序丢失问题

重写ModelViewSet的get_queryset方法时，已经得到了符合预期排序的候选人列表，但API实际返回的响应数据顺序错乱，和控制台打印的QuerySet预期顺序不一致。

相关代码 #

serializer.py #

class CandidateListSerializer(serializers.ModelSerializer):
    """
    Candidate serializer
    """

    job_skill_detail = serializers.SerializerMethodField(read_only=True)
    job_role_detail = serializers.SerializerMethodField(read_only=True)
    primary_skills_detail = serializers.SerializerMethodField(read_only=True)

    def get_job_skill_detail(self, obj):
        if obj.job_skill:
            return obj.job_skill.name

    def get_job_role_detail(self, obj):
        if obj.job_role:
            return obj.job_role.name

    def get_primary_skills_detail(self, obj):
        return obj.primary_skills.all().values(
            "id",
            "name",
        )

    class Meta:
        model = Candidate
        fields = "__all__"

view.py中get_queryset实现 #

def get_queryset(self):
        if "Admin" in self.request.user.role or "Recruiter" in self.request.user.role:
            return self.queryset
        if "Client" in self.request.user.role:
            candidates_source_to_client = CandidateSubmission.objects.filter(company=self.request.user.company).values_list('candidate', flat=True)
            print(f"Candidates id from candidate submission model: {candidates_source_to_client}")
            candidates = self.queryset.filter(
                Q(
                    band__in=CompanyBands.objects.filter(
                        company=self.request.user.company
                    ).values_list("band", flat=True)
                )
                | Q(id__in=candidates_source_to_client)
            )
            print(f"candidates from database with cliend company and company band: {candidates}")
            candidates_final = candidates.annotate(
                relevancy=Count(Case(When(id__in=candidates_source_to_client, then=1)))
            ).order_by("-relevancy")
            print(f"final candidates list: {candidates_final}")
            return candidates_final

控制台打印的预期排序结果 #

final candidates list: <QuerySet [<Candidate: Amresh Giri>, <Candidate: Dishita Vishwakarma>, <Candidate: Rhaan Kumaad 2>, <Candidate: Raan Kaad>, <Candidate: Vigneshkumar Chinnadurai>, <Candidate: Rhan Kumad>, <Candidate: Apurva Dhakre>, <Candidate: Saiba Kumar>, <Candidate: Ramona>]

• 排序规则不唯一：当前仅按-relevancy字段降序排序，所有在candidates_source_to_client中的候选人relevancy值为1，其余候选人为0。SQL查询中，当排序字段值相同时，数据库不保证返回结果的固定顺序，控制台打印QuerySet时触发的查询结果顺序，和后续DRF分页、序列化时触发的查询结果顺序可能不一致。
• 排序规则被覆盖：如果视图配置了OrderingFilter过滤器，且请求携带了ordering参数，会覆盖get_queryset中定义的排序规则；自定义分页类如果重写了queryset排序逻辑，也会导致顺序错乱。

• 给排序规则补充唯一的次级排序字段，保证同relevancy分值的记录也有固定排序规则，比如以主键id作为次级排序字段：

candidates_final = candidates.annotate(
    relevancy=Count(Case(When(id__in=candidates_source_to_client, then=1)))
# 次级排序可根据需求选择"-id"倒序或"id"正序
).order_by("-relevancy", "-id")

• 如果视图全局配置了OrderingFilter不需要开放自定义排序，直接将其从当前视图的filter_backends中移除；如果需要保留自定义排序能力，可显式指定视图默认排序，避免被异常参数覆盖：

class CandidateViewSet(viewsets.ModelViewSet):
    queryset = Candidate.objects.all()
    serializer_class = CandidateListSerializer
    # 显式指定默认排序
    ordering = ["-relevancy", "-id"]

• 修改后可直接打印QuerySet的query属性查看生成的SQL，确认ORDER BY子句包含上述两个排序字段、无额外排序逻辑即可。

内容的提问来源于stack exchange，提问作者Aman Mishra