Django: how to annotate queryset with count of filtered ForeignKey field?


Question

Django novice question :)

I have the following models - each review is for a product, and each product has a department:

class Department(models.Model):
    code = models.CharField(max_length=16)
class Product(models.Model):
    id = models.CharField(max_length=40, primary_key=True, db_index=True)
    dept = models.ForeignKey(Department, null=True, blank=True, db_index=True)
class Review(models.Model):
    review_id = models.CharField(max_length=32, primary_key=True, db_index=True) 
    product = models.ForeignKey(Product, db_index=True) 
    time = models.DateTimeField(db_index=True) 

I'd like to make a Django query for a date range (2012-01-01 to 2012-01-08) and return a list of all departments, annotated with department ID, and the number of products from that department that were reviewed during that date range.

This is frying my brain a bit :)

I can get all the reviews for a time range:

 reviews = Review.filter(time__range=["2012-01-01", "2012-01-08"])

Then I guess each review has a product field, and each of those products has a department code. But how can I group them by product and code, with counts and department IDs?

Alternatively, is it best to request the departments, and then annotate them with product counts, somehow?

1
33
7/10/2012 5:19:02 PM

Avoid extra and raw whenever possible. The aggregation docs have nearly this use case:

Straight from the docs:

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73

So, to modify this for your particular example:

depts = Department.objects.
            filter(product__review__time__range=["2012-01-01", "2012-01-08"]).
            annotate(num_products=Count('product'))

The function calls on separate lines is just for readability and you should move them about accordingly. I haven't tested this, but I think it should work.

38
7/11/2012 2:54:10 AM

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon