英文:
Optimize Django query in reverse lookup
问题
我有2个模型：
class Chassis(models.Model):
number = models.CharField(max_length=10, unique=True)
class Eir(models.Model):
chassis = models.ForeignKey(
'management.Chassis',
null=True,
blank=True,
related_name='eir',
on_delete=models.SET_NULL
)
number = models.IntegerField(unique=True, default=0)
我通过Django CLI 进行了测试：
>>> chassis = Chassis.objects.prefetch_related('eir')
>>> chassis
>>> chassis.first().eir.all()
我尝试了上面的命令，但仍然执行了与eir表相关的查询，如下所示：
# 从">>> chassis"生成
{"Time":[2023-03-07 08:25:42 UTC], Host:10.116.1.165} LOG: duration: 0.238 ms statement: SELECT "management_chassis"."id", "management_chassis"."number" FROM "management_chassis" LIMIT 21
{"Time":[2023-03-07 08:25:42 UTC], Host:10.116.1.165} LOG: duration: 0.777 ms statement: SELECT "rental_eir"."id", "rental_eir"."number", "rental_eir"."chassis_id" FROM "rental_eir" WHERE "rental_eir"."chassis_id" IN (1292, 104)
# 从">>> chassis.first().eir.all()"生成
{"Time":[2023-03-07 08:25:59 UTC], Host:10.116.1.165} LOG: duration: 0.239 ms statement: SELECT "rental_eir"."id", "rental_eir"."number", "rental_eir"."chassis_id" FROM "rental_eir" WHERE "rental_eir"."chassis_id" IN (1292)
我原本期望不再有第三个查询，因为数据应该存储在内存中。
我的真正目的与Django REST Framework 中的def to_representation相关，其中进行了反向查找，比如ret['eir'] = instance.eir.all().last().id，其中instance是Chassis模型。
总的来说，我试图减少不必要的查询。
英文:
I have 2 models
class Chassis(models.Model):
number = models.CharField(max_length=10, unique=True)
class Eir(models.Model):
chassis = models.ForeignKey(
'management.Chassis',
null=True,
blank=True,
related_name='eir',
on_delete=models.SET_NULL
)
number = models.IntegerField(unique=True, default=0)
I tested via Django CLI
>>> chassis = Chassis.objects.prefetch_related('eir')
>>> chassis
>>> chassis.first().eir.all()
I tried the commands above and it still did a query with the eir table as shown below:
# produced from ">>> chassis"
{"Time":[2023-03-07 08:25:42 UTC], Host:10.116.1.165} LOG: duration: 0.238 ms statement: SELECT "management_chassis"."id", "management_chassis"."number" FROM "management_chassis" LIMIT 21
{"Time":[2023-03-07 08:25:42 UTC], Host:10.116.1.165} LOG: duration: 0.777 ms statement: SELECT "rental_eir"."id", "rental_eir"."number", "rental_eir"."chassis_id" FROM "rental_eir" WHERE "rental_eir"."chassis_id" IN (1292, 104)
# produced from ">>> chassis.first().eir.all()"
{"Time":[2023-03-07 08:25:59 UTC], Host:10.116.1.165} LOG: duration: 0.239 ms statement: SELECT "rental_eir"."id", "rental_eir"."number", "rental_eir"."chassis_id" FROM "rental_eir" WHERE "rental_eir"."chassis_id" IN (1292)
I was expecting that there will be no 3rd query anymore as the data will be stored in memory.
My real purpose is related to Django REST Framework manipulating the returned data in def to_representation where a reverse lookup is done like ret['eir'] = instance.eir.all().last().id where instance is Chassis model.
Generally, I am trying to reduce unnecessaty queries produced.
答案1
得分: 1
.first() 不会读取缓存。
如果你想减少查询的次数，你需要将查询集转换为列表。
但是，如果你不需要整个"chassis"列表，chassis.first().eir.all() 会产生更轻量级的查询（因为使用了limit 1）。
>>> chassis = list(Chassis.objects.prefetch_related('eir'))
>>> chassis
>>> chassis[0].eir.all()
然而，这与你描述的序列化器用例有点不同。
将 ret['eir'] = instance.eir.all().last().id 替换为 ret['eir'] = instance.eir.all()[-1].id 应该会产生最小的查询，如果 "eir" 被预取。
英文:
.first() does not read the cache.
If you want to reduce the number of queries you'll need to cast the queryset in a list.
However if you don't need the entire "chassis" list, chassis.first().eir.all() result in leaner queries (tanks to the limit 1).
>>> chassis = list(Chassis.objects.prefetch_related('eir'))
>>> chassis
>>> chassis[0].eir.all()
However, this a bit different compared to the usecase you described with the serializer.
Replacing ret['eir'] = instance.eir.all().last().id by ret['eir'] = instance.eir.all()[-1].id should produce minimal queries if "eir" is prefetch
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论