Filter annotate() COUNT()

If you use annotate() function in your Django query and want to apply filters for it, you won’t be able to do it.
This is where extra() function can be used.

For example, here is my hotel app:

from django.db import models
class Hotel(models.Model):
    name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

class Review(models.Model):
    is_active = models.BooleanField(default =  False)
    content = models.CharField(max_length=500)
    hotel = models.ForeignKey(Hotel)

If I make search for hotels, that are situated in Paris and in my result view I want to display number of reviews for each object. I do this way:

from django.db.models import Count
hotels = Hotel.objects.filter(city = "Paris").annotate(review_count = Count(review))

But if I want to count only those reviews, that are active, I can not add one more filter() function, because conditions in all filter() function are combined together in WHERE statement of resulting SQL query, but I do not want to filter Hotel objects by hotel_review.is_active = True condition.

To tackle this we can do the following:

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'review_count': 'SELECT COUNT(*) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

How to use review_count parameter in template and JSON objects see article about Extra Select Field in JSON