如何在Django中对特定的prefetch_related()应用任意过滤器?

Question

我正在尝试优化API的触发查询。我有四个模型，分别是User, Content, Rating和usererrating，它们之间有一定的关系。我希望相应的API返回所有现有内容以及它们的评级计数以及特定用户给出的分数。

我曾经做过这样的事情:Content.objects.all()作为一个查询集，但我意识到，在拥有大量数据的情况下，将会触发大量查询。因此，我已经做了一些努力来优化使用select_related()和prefetch_related()触发的查询。然而，我正在处理一个额外的python搜索，我希望删除它，使用受控的prefetch_related()-在嵌套的prefetch和select中为特定的prefetch应用过滤器。

这些是我的模型:

from django.db import models
from django.conf import settings
class Content(models.Model):
title = models.CharField(max_length=50)
class Rating(models.Model):
count = models.PositiveBigIntegerField(default=0)
content = models.OneToOneField(Content, on_delete=models.CASCADE)
class UserRating(models.Model):
user = models.ForeignKey(
settings.AUTH_USER_MODEL, blank=True, null=True, on_delete=models.CASCADE
)
score = models.PositiveSmallIntegerField()
rating = models.ForeignKey(
Rating, related_name="user_ratings", on_delete=models.CASCADE
)
class Meta:
unique_together = ["user", "rating"]

到目前为止我所做的是:

contents = (
Content.objects.select_related("rating")
.prefetch_related("rating__user_ratings")
.prefetch_related("rating__user_ratings__user")
)
for c in contents:  # serializer like
user_rating = c.rating.user_ratings.all()
for u in user_rating:  # how to remove this dummy search?
if u.user_id == 1:
print(u.score)

查询:

(1) SELECT "bitpin_content"."id", "bitpin_content"."title", "bitpin_rating"."id", "bitpin_rating"."count", "bitpin_rating"."content_id" FROM "bitpin_content" LEFT OUTER JOIN "bitpin_rating" ON ("bitpin_content"."id" = "bitpin_rating"."content_id"); args=(); alias=default
(2) SELECT "bitpin_userrating"."id", "bitpin_userrating"."user_id", "bitpin_userrating"."score", "bitpin_userrating"."rating_id" FROM "bitpin_userrating" WHERE "bitpin_userrating"."rating_id" IN (1, 2); args=(1, 2); alias=default
(3) SELECT "users_user"."id", "users_user"."password", "users_user"."last_login", "users_user"."is_superuser", "users_user"."first_name", "users_user"."last_name", "users_user"."email", "users_user"."is_staff", "users_user"."is_active", "users_user"."date_joined", "users_user"."user_name" FROM "users_user" WHERE "users_user"."id" IN (1, 4); args=(1, 4); alias=default

正如你所看到的，在上面触发的查询中，我只有三个查询，而不是过去发生的太多查询。然而，我想我可以在我的最新查询-users_user"."id" IN (1,)上使用过滤器来删除python搜索(第二个for循环)。根据这篇文章和我的努力，我不能在第三个查询上应用.filter(rating__user_ratings__user_id=1)。实际上，我无法匹配我的问题使用Prefetch(..., queryset=...)实例给出这个答案。

Answer 1

我认为你正在寻找预取对象:https://docs.djangoproject.com/en/4.0/ref/models/querysets/prefetch-objects

试试这个:

from django.db.models import Prefetch
contents = Content.objects.select_related("rating").prefetch_related(
Prefetch(
"rating__user_ratings",
queryset=UserRating.objects.filter(user__id=1),
to_attr="user_rating_number_1",
)
)
for c in contents:  # serializer like
print(c.rating.user_rating_number_1[0].score)