如何合并两个查询集Django [英] How to merge two querysets django

问题描述

我正在尝试获取最近100条帖子的列表，以及该帖子用户的已批准，待处理和已拒绝帖子的总数.

I'm trying to get a list of latest 100 posts and also the aggregated count of approved, pending, and rejected posts for the user of that post.

models.py

class BlogPost(models.Model):
    POST_STATUSES = (
                    ('A', 'Approved'),
                    ('P', 'Pending'),
                    ('R', 'Rejected')
                    )
    author = models.ForeignKey(User)
    title = models.CharField(max_length=50)
    description = models.TextField()
    status = models.ChoiceField(max_length=1, choices=POST_STATUSES)

---

views.py

现在我正在像这样获取汇总计数，但是我对如何将计数与帖子标题合并感到困惑

Now I'm getting the the aggregated count like so, but I'm confused on how to merge the count with the title of the posts

top_post_users = list(BlogPost.objects.values_list('user_id', flat=True))[:100]
users =  User.objects.filter(pk__in=top_post_users).annotate(approved_count=Count(Case(When(user_posts__status="A", then=1),output_field=IntegerField()))).annotate(pending_count=Count(Case(When(user_posts__status="P", then=1),output_field=IntegerField()))).annotate(reject_count=Count(Case(When(user_posts__status="R", then=1),output_field=IntegerField())))
users.values('approved_count', 'pending_count', 'reject_count')

---

这是我想要的结果:

---

This is the result I want:

• 职务标题，作者1、10、5、1
• 帖子标题2，作者2、7、3、1
• 帖子标题3，作者1、10、5、1

如何将返回的计数与标题合并?

我知道我可以使用for循环并附加每个循环，但是出于效率考虑，我认为这不是正确的方法.有没有使用Django数据库ORM的更有效方法?

I know I could use a for loop and append each one, but efficiency wise I don't think this is the right way to do it. Is there a more efficient way using django database ORM?

我已经尝试过了

users.values(标题"，已批准的计数"，待审核的计数"，拒绝的计数")

users.values('title', approved_count', 'pending_count', 'reject_count')

...并且行得通，但返回的信息超过了最近的100条，所以我认为它可以获取这些用户的所有信息和总计的信息.

...and that works, but it returns more than the latest 100 posts, so I think it's getting all the posts for those users and the aggregated count.

推荐答案

最终，您需要一个BlogPost列表:

Ultimately, you want a list of BlogPosts:

main_qs = BlogPost.objects
# add filters, ordering etc. of the posts

，您不仅要在帖子标题旁边显示作者，还要用带注释的计数丰富作者信息.

and you want to display not only the authors next to the title of the post but also enrich the author information with the annotated counts.

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

# you need subqueries to annotate the blog posts
base_sub_qs = BlogPost.objects.filter(author__pk=OuterRef('author__pk'))

# add subqueries for each count
main_qs = main_qs.annotate(
    user_approved_count=Subquery(base_sub_qs.filter(status="A").annotate(
            c=Count('*')).values('c'), output_field=IntegerField()),
    user_pending_count=Subquery(base_sub_qs.filter(status="P").annotate(
            c=Count('*')).values('c'), output_field=IntegerField()),
    user_rejected_count=Subquery(base_sub_qs.filter(status="R").annotate(
            c=Count('*')).values('c'), output_field=IntegerField()),
)

然后您可以在模板中访问它们:

You can then access these in your template:

{% for post in posts %}
    {{ post.title }}
    {{ post.author.get_full_name }}
    approved: {{ post.user_approved_count }}
    pending: {{ post.user_pending_count }}
    rejected: {{ post.user_rejected_count }}
{% endfor %}

文档: https://docs.djangoproject.com/zh-CN/2.1/ref/models/expressions/#subquery-expressions

这篇关于如何合并两个查询集Django的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！