Tastypie Dehydrate reverse relation count

Question

I have a simple model which includes a product and category table. The Product model has a foreign key Category.

When I make a tastypie API call that returns a list of categories /api/vi/categories/ I would like to add a field that determines the "product count" / the number of products that have a giving category. The result would be something like:

category_objects[
{ 
   id: 53
   name: Laptops
   product_count: 7
}, 
...
 ]

The following code is working but the hit on my DB is heavy

    def dehydrate(self, bundle):
        category = Category.objects.get(pk=bundle.obj.id)
        products = Product.objects.filter(category=category)
        bundle.data['product_count'] = products.count()
        return bundle  

Is there a more efficient way to build this query? Perhaps with annotate ?

Answer 1

Your code does additional count query for each category. You're right about annotate being helpfull in this kind of a problem.

Django will include all queryset's fields in GROUP BY statement. Notice .values() and empty .group_by() serve limiting field set to required fields.

cat_to_prod_count = dict(Product.objects
                                .values('category_id')
                                .order_by()
                                .annotate(product_count=Count('id'))
                                .values_list('category_id', 'product_count'))

The above dict object is a map [category_id -> product_count].

It can be used in dehydrate method:

 bundle.data['product_count'] = cat_to_prod_count[bundle.obj.id]

If that doesn't help, try to keep similar counter on category records and use singals to keep it up to date.

Note categories are usually a tree-like beings and you probably want to keep count of all subcategories as well.

In that case look at the package django-mptt.