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?
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.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.