python 使用prefetch_related减少ManyToMany字段中的查询

yv5phkfx  于 2023-05-05  发布在  Python
关注(0)|答案(1)|浏览(177)

我想进一步减少查询的数量。我使用prefetch_related来减少查询的数量。我想知道是否有可能减少到一个查询。请让我展示一下相关代码:
我有一个prefetch_related的视图:

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

我有序列化器使用的模型。在这里,重要的是要注意我想要与reserve_id和reserve_distance沿着显示的混合属性名称:
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)

在模型IndianReserveBandName中获取Name字段。
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)

然后,主序列化器使用BenefitIndianReserveBandSerializer来获得字段reserve_id、reserve_distance和name:
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')

以上是导致两个问题,我想成为一个:

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只创建一个查询的东西?
多谢了

xdnvmnnf

xdnvmnnf1#

我是不是错过了什么让Django只创建一个查询的东西?

,使用两个查询的行为是有意为之。它可以防止引入 * 数据重复 *,其中相同列的相同值重复 * 很多 *。这可能会导致内存使用量激增(在数据库端和Django/Python端),并导致系统无响应。事实上,它甚至会导致内存不足(OOM)管理器杀死Web应用程序、数据库或其他应用程序。

相关问题