How to merge two querysets django

Question

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:

Title Of Post, Author1, 10, 5, 1
Title Of Post2, Author2, 7, 3, 1
Title Of Post3, Author1, 10, 5, 1

How can I merge the returned counts with the titles?

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?

I've tried this

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

...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.

If you can cast the queryset into a different data structure, like a list, I'm pretty sure pandas has tools that would make this easier. Not sure offhand what they are though — Green Cloak Guy
– Green Cloak Guy, Commented Mar 20, 2019 at 19:13
I suppose I could use pandas.. I roughly know how to merge using it, but I'd like to keep it as a database query operation @Green Cloak Guy — onyx
– onyx, Commented Mar 20, 2019 at 19:15

Risadinha · Accepted Answer · 2019-03-20 20:20:05Z

2

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 %}

Documentation: https://docs.djangoproject.com/en/2.1/ref/models/expressions/#subquery-expressions

edited Mar 20, 2019 at 20:20

answered Mar 20, 2019 at 19:32

Risadinha

16.8k2 gold badges97 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

onyx Over a year ago

I am getting an error: 'Query' object has no attribute 'contains_aggregate'

Risadinha Over a year ago

I've changed the code. Trying to convert my working code to your use case. I thought your code might allow a simpler version, but you still need Subquery to make the OuterRef work.

onyx Over a year ago

Now I am getting this: Subquery returns more than 1 row

Risadinha Over a year ago

values('c') is meant to prevent that. Please make sure that you have added it to all the subqueries.

onyx Over a year ago

Yeah, that's why I was confused, it looks right and makes sense logically, but its returning more than one row still...

|

Collectives™ on Stack Overflow

How to merge two querysets django

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related