CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-orm
pistacchio
pistacchio

Reputation: 58863

Django: annotate Count with filter

I have "post" objects and a "post like" object with how many likes a post has received by which user:

class Post(models.Model):
    text     = models.CharField(max_length=500, default ='')
    user     = models.ForeignKey(User)

class PostLike(models.Model):
    user    = models.ForeignKey(User)
    post    = models.ForeignKey(Post)

I can select how many likes a post has received like this:

Post.objects.all().annotate(likes=Count('postlike'))

This roughly translates to:

SELECT p.*,
       Count(l.id) AS likes
    FROM post p, postlike l
    WHERE p.id = l.post_id
    GROUP BY (p.id)

It works. Now, how I can filter the Count aggregation by the current user? I'd like to retrieve not all the likes of the post, but all the likes by the logged user. The resulting SQL should be like:

SELECT p.*,
    (SELECT COUNT(*) FROM postlike WHERE postlike.user_id = 1 AND postlike.post_id = p.id) AS likes
FROM post p, postlike l
WHERE p.id = l.post_id
GROUP BY (p.id)

Upvotes: 2

Views: 9069

Answers (3)

Krzysztof Szularz
Krzysztof Szularz

Reputation: 5249

Do you know that Count has a filter argument?

Post.objects.annotate(
    likes=Count('postlike', filter=Q(postlike__user=logged_in_user))
)

Upvotes: 13

AKX
AKX

Reputation: 168873

It's not exactly as clean, but you could use Case/When...

posts = Post.objects.all().annotate(likes=models.Count(
  models.Case(
    models.When(postlike__user_id=user.id, then=1),
    default=0,
    output_field=models.IntegerField(),
  )
))

And of course, you can always drop down to .extra() or even raw SQL when there's something you can't express via the Django ORM.

Upvotes: 1

neverwalkaloner
neverwalkaloner

Reputation: 47354

Try to add filter first:

Post.objects.filter(postlike__user=request.user).annotate(likes=Count('postlike'))

From the docs:

The filter precedes the annotation, so the filter constrains the objects considered when calculating the annotation.

Upvotes: 1

Related Questions