Django：如何为每个外键选择有限的行数？ [英] Django: How to select a limited amount of rows for each foreign key?

问题描述

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ = $ = 256）
comment = models.TextField（max_length = 1286）
created = models.DateTimeField（db_index = True）

现在我如何获取最新的评论，但是只将结果限制在同一用户（例如3）的评论中？

在纯SQL中完成：如何选择每个外键的行数有限？

解决方案

我猜，这可以工作：

  Comment.objects.filter（* [
〜Q（id__in = user.comments.order_by（' -  created'）[3 ：]。values_list（'id'，flat = True））
在User.objects.all（）中的用户
]）
  

然而，似乎既不是pythonic也不是效率方法解决问题。 （效率问题可能会通过使用缓存来解决，但仍然存在。）

你想要实现什么？您只需获取所有用户的评论，并仅使用最新的三个。假设您的评论是由 -created 字段排序：

  {％对于用户在用户％} 
 {％在user.comments.all |切片中的评论：3％} {{comment}} {％endfor％} 
 {％endfor％} 
  

在SQL查询中，切片将转换为 LIMIT 所以你不会得到所有的评论。当然，执行一个查询可以获得所有的注释，所以使用模板缓存可以帮助。

再次，这可能在你的情况下没有意义。如果不符合要求，则会更好。

原始SQL

如果您知道如何使用原始SQL（[1]可能有帮助）），然后您需要找到一种方法将其放入 extra（） 某种方式（[2]是复杂的额外查询的例子，你的想法）。

[1] 如何为每个外键选择有限的行数

[2] django的.extra（其中=子句被table-重命名.filter（foo__in = ... subselects

class Comment (models.Model):
        user = models.ForeignKey(User, related_name="comments")
        title = models.TextField(max_length=256)
        comment = models.TextField(max_length=1286)
        created = models.DateTimeField(db_index=True)

Now how do I get latest comments but limit the results to only (say 3) comments per same user?

Here is how its done in pure SQL: How to select a limited amount of rows for each foreign key?

解决方案

I guess, this would work:

Comment.objects.filter(*[
    ~Q(id__in=user.comments.order_by('-created')[3:].values_list('id', flat=True))
    for user in User.objects.all()
])

However, it seems like neither pythonic nor efficient way to solve the problem. (Efficiency problem could be somewhat solved by using cache, but still.)

What are you trying to achieve, anyway? You could just fetch all user's comments and use only the latest three. Assuming that your comments are ordered by -created field:

{% for user in users %}
    {% for comment in user.comments.all|slice:"3" %}{{ comment }}{% endfor %}
{% endfor %}

Slicing will be converted to LIMIT clause in SQL query, so you won't be getting all the comments anyway. Of course, there would be more that one query executed to get all the comments, so using template caching can help.

Again, this may not make sense in your case. If it doesn't, it would be better if you clarify the requirements.

Raw SQL

If you know how to do what you want with raw SQL ([1] may help), then you need to just find a way to put it into extra() somehow ([2] is an example of complex extra query which may give you an idea).

[1] How to select a limited amount of rows for each foreign key?

[2] django's .extra(where= clauses are clobbered by table-renaming .filter(foo__in=... subselects

这篇关于Django：如何为每个外键选择有限的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！