Django: annotate Count with filter

Question

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)

Answer 1

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.