如何将Django中ManyToMany关联的查询优化为单查询？

我希望进一步减少查询次数，目前已使用prefetch_related降低了查询量，但想知道能否将查询缩减至1次。以下是相关代码：

视图代码 #

class BenefitList(generics.ListAPIView):
    serializer_class = BenefitGetSerializer

    def get_queryset(self):
        queryset = Benefit.objects.all()
        queryset = queryset.filter(deleted=False)

        qs=  queryset.prefetch_related('nearest_first_nations__reserve_id')
    
        return qs

模型定义 #

benefit.py #

class IndianReserveBandDistance(models.Model):
    reserve_id = models.ForeignKey(IndianReserveBandName,
                                   on_delete=models.SET_NULL,
                                   db_column="reserve_id",
                                   null=True)
    reserve_distance = models.DecimalField(max_digits=16, decimal_places=4, blank=False, null=False)

    @property
    def name(self):
        return self.reserve_id.name
        
class Benefit(models.Model):
    banefit_name = models.TextField(blank=True, null=True)
    nearest_first_nations = models.ManyToManyField(IndianReserveBandDistance,
                                                   db_column="nearest_first_nations",
                                                   blank=True,
                                                   null=True)   

indian_reserve_band_name.py #

class IndianReserveBandName(models.Model):
    ID_FIELD = 'CLAB_ID'
    NAME_FIELD = 'BAND_NAME'

    name = models.CharField(max_length=127)
    band_number = models.IntegerField(null=True)

序列化器 #

get.py #

class BenefitGetSerializer(serializers.ModelSerializer):
    nearest_first_nations = BenefitIndianReserveBandSerializer(many=True)

distance.py #

class BenefitIndianReserveBandSerializer(serializers.ModelSerializer):
    class Meta:
        model = IndianReserveBandDistance
        fields = ('reserve_id', 'reserve_distance', 'name')

当前执行生成的2次查询 #

SELECT ("benefit_nearest_first_nations"."benefit_id") AS "_prefetch_related_val_benefit_id",
       "indianreservebanddistance"."id",
       "indianreservebanddistance"."reserve_id",
       "indianreservebanddistance"."reserve_distance"
  FROM "indianreservebanddistance"
 INNER JOIN "benefit_nearest_first_nations"
    ON ("indianreservebanddistance"."id" = "benefit_nearest_first_nations"."indianreservebanddistance_id")
 WHERE "benefit_nearest_first_nations"."benefit_id" IN (1, 2)
 
 
 SELECT "indianreservebandname"."id",
       "indianreservebandname"."name"
  FROM "indianreservebandname"
 WHERE "indianreservebandname"."id" IN (678, 140, 627, 660, 214, 607)
 ORDER BY "indianreservebandname"."id" ASC

期望的单查询 #

SELECT ("benefit_nearest_first_nations"."benefit_id") AS "_prefetch_related_val_benefit_id",
       "indianreservebanddistance"."id",
       "indianreservebanddistance"."reserve_id",
       "indianreservebanddistance"."reserve_distance",
       "indianreservebandname"."name"
  FROM "indianreservebanddistance"
 INNER JOIN "benefit_nearest_first_nations"
    ON ("indianreservebanddistance"."id" = "benefit_nearest_first_nations"."indianreservebanddistance_id")
 inner JOIN  "indianreservebandname"
    on ("indianreservebandname"."id" = "indianreservebanddistance"."reserve_id")
 WHERE "benefit_nearest_first_nations"."benefit_id" IN (1, 2)

问题 #

是否可以实现单查询？是哪里阻碍了Django生成单查询？

解决方案 #

可以实现单查询，核心问题是默认的prefetch_related会分两次执行查询：先批量获取关联的IndianReserveBandDistance记录，再根据这些记录的reserve_id批量查询IndianReserveBandName。要合并成一次查询，需要用Prefetch对象配合select_related来强制Django在预取时关联查询外键表。

修改后的视图代码 #

from django.db.models import Prefetch

class BenefitList(generics.ListAPIView):
    serializer_class = BenefitGetSerializer

    def get_queryset(self):
        queryset = Benefit.objects.filter(deleted=False)
        # 自定义预取规则，让查询IndianReserveBandDistance时直接关联reserve_id对应的表
        prefetch = Prefetch(
            'nearest_first_nations',
            queryset=IndianReserveBandDistance.objects.select_related('reserve_id')
        )
        qs = queryset.prefetch_related(prefetch)
        return qs

原理说明 #

• 原代码中prefetch_related('nearest_first_nations__reserve_id')的逻辑是"预取nearest_first_nations，再预取这些记录的reserve_id"，本质是两次独立的批量查询。
• 通过Prefetch对象指定预取的查询集时，给IndianReserveBandDistance加上select_related('reserve_id')，Django会在查询IndianReserveBandDistance时直接JOINIndianReserveBandName表，一次性拉取所有需要的数据，最终合并为你期望的单条SQL查询。

另外注意：模型Benefit中的banefit_name是拼写错误，建议修正为benefit_name。

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