Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 922
flossfan Avatar asked Jul 10 '12 17:07

flossfan


People also ask

How do you annotate in Django QuerySet?

Appending the annotate() clause onto a QuerySet lets you add an attribute to each item in the QuerySet, like if you wanted to count the amount of articles in each category. However, sometimes you only want to count objects that match a certain condition, for example only counting articles that are published.

Can I filter a QuerySet Django?

The filter() method is used to filter you search, and allows you to return only the rows that matches the search term.

How can I filter a Django query with a list of values?

To filter a Python Django query with a list of values, we can use the filter method with in . to search Blog entries with pk set to 1,4 or 7 by calling Blog. objects. filter with the pk_in argument set to [1, 4, 7] .

What is the difference between aggregate and annotate in Django?

Aggregate calculates values for the entire queryset. Annotate calculates summary values for each item in the queryset.


1 Answers

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.

like image 194
Josh Smeaton Avatar answered Sep 20 '22 07:09

Josh Smeaton