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

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.

In django >= 2.0 this could be achieved with

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

For more info read docs https://docs.djangoproject.com/en/2.0/topics/db/aggregation/#filtering-on-annotations

I’ve had to do a couple of similar queries in the last few days and the easiest way it to use the extra queryset function to annotate each object in your queryset with a filtered count of the products:

start = ..  # need to be formatted correctly
end = ...
departments = Departments.objects.all().extra(select = {
     'product_count' : """ SELECT COUNT(*) FROM appname_department
                           JOIN appname_product
                               ON appname_product.dept_id = appname_department.id
                           JOIN appname_review 
                               ON appname_review.product_id = appname_product.id
                           WHERE appname_review.time BETWEEN %s AND %s
                       """
}, params=[start, end])

and

{% for department in departments %}
    {{ department.product_count }}
{% endfor %}

The Docs for aggregation
https://docs.djangoproject.com/en/dev/topics/db/aggregation/#cheat-sheet

There probably is a way to use aggregate or annotate, but I prefer this:

departments = Department.objects.all()
for dept in departments : 
    # Get the number of reviewed products for a given range and department
    num_products = dept.product_set.filter(review__time__range=["2012-01-01", "2012-01-08"]).count()

if you absolutely need it as a function of the model:

class Department(models.Model) :
    ...
    def num_products(self, start_date, end_date) : 
        return self.product_set.filter(review__time__range=[start_date, end_date]).count()

EDIT

I think that if you were to do a raw query (something like this)

sql = """SELECT COUNT(Product.*) as num_products, Department.*
    FROM Department
    LEFT OUTER JOIN Product ON Product.department = Department.id
    LEFT OUTER JOIN Review ON Product.id = Review.product
    WHERE Review.time BETWEEN "2012-01-01" AND "2012-01-08"
    GROUP BY Department.id"""

Department.objects.raw(sql)

and then num_products will be an attribute on every Dept instance in the results.

you may need to play with the field + table names a little

I have the same situation with similar data model.

My solution was like:

 Department.objects \
.extra(where=["<review_table_name.time_field> BETWEEN <time1> AND <time2> "])\
.annotate(num_products=Count('product__review__product_id'))