Django中使用prefetch_related限制查询数量

Question

有没有一种方法可以告诉prefetch_related仅获取有限的相关对象？ 假设我正在获取用户列表并且我知道我想获取他们最近的评论。 而不是在循环中为每个用户获取评论，我使用prefetch_related在获取用户时预先获取它们。 我的理解是，这将获取原始查询结果中任何用户发表的所有评论，但我只想显示每个用户的最新5条评论。

如果评论列表非常庞大，这会如何影响性能？ 有没有一种方法可以在单个（或两个）查询中仅获取每个用户的5条评论？ 它不必与获取用户的原始查询相同，但那样做会很好。

我基本上想把这个转变成：

   users = User.objects.all()
   for user in users:
       user.comments.all()[:10]

变成这样

 User.objects.all().prefetch_related('comments', limit=10)

如果用户有100个或10000个评论，它们并不会全部加载到内存中。你如何在原始SQL中实现这样的功能？

- Owais Lone

---

我可以接受每个关系一个查询，但每个对象一个查询才是真正的杀手。 - Owais Lone

如果与被选用户相关联的评论表有成千上万行，将会怎样呢？虽然这不太可能在用户-评论关系中发生，但在其他情况下非常可能。我担心每次选择10-20个用户（考虑分页）时，在Python中获取所有数以百计或上千条评论并对它们进行连接将会导致性能问题。 - Owais Lone

这就是为什么你不能在同一个视图中同时使用prefetch_related和分页。它会在限制主查询之前预取整个查询集。这样做并不好。 - Dan Gayle

1.7版本中的Prefetch对象能在一定程度上帮助解决这个问题吗？或者使用带有reverse的queryset？（https://docs.djangoproject.com/en/1.7/ref/models/querysets/#prefetch-related） - wasabigeek

这个问题将在Django 4.2中得到修复，详见解决#26780问题的提交。让我们等待发布，此时我建议也使用子查询。 - Stark Sim

Answer 1

我认为现在在 Django 的新版本中已经有了解决方法，因为我们有 OuterRef 和 Subquery。

from django.db.models import OuterRef, Subquery, Prefetch

subqry = Subquery(Comment.objects \
    .filter(user_id=OuterRef('user_id')) \
    .values_list('id', flat=True)[:5])

User.objects.prefetch_related(
    Prefetch('comments', queryset=Comment.objects.filter(id__in=subqry)))

- haseebahmad

---

你看到它生成的可怕的SQL了吗？你检查过它的性能吗？ - deathangel908

我不确定是否能生成更好的 SQL 以实现主结果列表下的嵌套有限行。我刚刚想出了一种在 Django ORM 中实现嵌套查询限制的方法。如果您想到任何更好的原始 SQL，请分享，我们可能能够找到为其生成 Django ORM 代码的方法。 - haseebahmad

@Desh 我认为你可以将下面的语句 "Prefetch('comments', queryset=Comment.objects.filter(id__in=subqry))" 作为第二个参数传递给prefetch_related_objects。 虽然我还没有测试过，但我认为它会起作用。 - haseebahmad

在MySQL中生成错误：这个版本的MySQL还不支持'LIMIT＆IN / ALL / ANY / SOME子查询 - Rockallite

到了2021年，6年过去了，这仍然看起来是唯一的解决方案。可惜它没有被选为最佳答案。也许不是最优雅的，但这是我找到的唯一有效的方法。上面链接的Django票据已经5年了，而且在过去的2年里都没有活动。唯一给出的解决方案与此处相同，也是由haseebahmad提供的。 - Łukasz

Answer 2

似乎唯一限制预取相关对象数量的方法是使用Prefetch()并过滤字段。使用切片无法实现此目的。

User.objects.all().prefetch_related(
    Prefetch('msg_sent', queryset=UserMsg.objects.order_by('-created')[:10]))

返回一个错误

AssertionError: Cannot filter a query once a slice has been taken.

似乎唯一限制相关对象数量的方法是使用值上的筛选器，例如：

from datetime import datetime, timedelta
timelimit = datetime.now() - timedelta(days=365)

User.objects.all().prefetch_related(
    Prefetch('msg_sent', queryset=UserMsg.objects.filter(created__gte=timelimit)))

虽然这不会返回一个固定的数字，在某些情况下它可能是有用的，并且它将减少预取对象的数量。

- C14L

---

这里有一张关于 Prefetch 对象不接受带有切片的查询集的票据：https://code.djangoproject.com/ticket/26780。 - Ludwik Trammer

已经在使用 Django 4.2，但目前仍处于 beta 版本。 - Serguei A

这个最后的小评论现在应该成为被接受的答案了 xD - undefined

Answer 3

这是在Django(2.1)中实际有效的方法（基于haseebahmad的回答）。
为了使prefetch_related接受自定义查询集：使用Prefetch
因此：

from django.db.models import OuterRef, Subquery ,Prefetch

User.objects.all().prefetch_related(Prefetch('comment_set',  
queryset=Comment.objects.filter(id__in= 
Subquery(Comment.objects.filter(user_id=OuterRef('user_id')).
values_list('id', flat=True)[:1]))))

- Rafi

Answer 4

也可以使用 CTE 和 ROW_NUMBER() 来实现。

from django.db.models import Prefetch
from django.db.models.functions.window import RowNumber
from django_cte import With

cte = With(
    Comment.objects.annotate(
        row_number=Window(
            expression=RowNumber(),
            partition_by=F("user_id")
        )
    )
)
qs = cte.with_cte(cte).filter(row_number<=10)
users = User.objects.prefetch_related(
    Prefetch("comments", queryset=qs, to_attr="limited_comments")
)

- gospodima