annotate sum of annotated values in related model

Question

I have these models:

class Product(..):
    category = ForeignKey('InputCategory...

class InputCategory(MPTTModel):
   route_to = ForeignKey('RoutingCategory...

class RoutingCategory(MPTTModel):
   pass

So InputCategory has many Products and RoutingCategory has many InputCategory objects.

I can annotate product_count for an InputCategory:

InputCategory.objects.annotate(product_count=Count('products'))

But I need to have product_count annotated to the RoutingCategory.

How can I do that?

I tried to modify Manager for InputCategory and do this:

RoutingCategory.objects.annotate(product_count=Sum('input_categories__product_count'))

But it says: FieldError: Unsupported lookup 'product_count' for AutoField or join on the field not permitted.

Obviously I should have override related Manager.

Do you know how to do that?

EDIT

@Willem Van Onsem came up with this answer which works:

from django.db.models import Count
RoutingCategory.objects.annotate(
    product_count=Count('input_categories__products')
)

But I have annotated also product_cumulative_count which counts products of self and all ancestors of the InputCategory. I would like to access this sum of this counts too from the RoutingCategory.

I can't figure out how.

Answer 1

First of all, a problem with this is that Django does not use the .objects manager to access the related model. But even if it did, it would not work (well). SQL does not allow such multilevel GROUP BYs, or at least not without first making a query, and then using that result in another query.

You however do not need that anyway, you can just Count the related products here:

from django.db.models import Count

RoutingCategory.objects.annotate(
    product_count=Count('input_categories__products')
)