Filter annotate() COUNT()

Posted: March 31st, 2010 | Author: | Filed under: Django | Tags: | 3 Comments »

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


3 Comments on “Filter annotate() COUNT()”

  1. 1 Silver said at 1:58 pm on March 9th, 2011:

    Well, thats not actually true. You can filter by active entities.

    Consider this example:

    [code]
    class ProjectManager(Manager):
    def get_query_set(self):
    return super(ProjectManager, self).get_query_set().filter(satellite__is_active=True)\
    .annotate(satellites=Count('satellite', distinct=True))\
    .annotate(keywords=Count('keyword', distinct=True))

    [/code]

    The key here is .fiter(model__field_bool=True)

  2. 2 Matt said at 9:52 am on July 21st, 2011:

    Silver: That won’t work here, because we still want the final queryset to include those elements where the count of active satellites is zero, and your filter clause will eliminate those. (In the hotels example: we still want to display hotels that have no active reviews.)

  3. 3 yellottyellott said at 1:24 pm on August 24th, 2011:

    @Silver
    That makes sense if I wanted to get those numbers by themselves. But is there a way to tack those onto a bigger query set in a similar manner? I’ve written raw sql that does some super wonky ‘count(distinct (case when (is_active=1) then object.id else NULL’ to achieve a similar effect, and I’m sure that’s why it’s not bundled into the ORM.

    What I think the author intended was to list all hotels, and next to each one show the number of reviews having is_active true. Your .filter(satellite__is_active=True) is the problem ekaterina was pointing out because that will chop out some hotels.

    @ekaterina
    Thanks for the post. This is pretty much the use case I just ran into.