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.

Milano · Accepted Answer · 2020-02-04 20:13:39Z

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')
)

edited Feb 4, 2020 at 20:13

Milano

18.9k47 gold badges177 silver badges388 bronze badges

answered Feb 4, 2020 at 20:06

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Milano Over a year ago

Maybe I have another (similar) problem. I've added an edit to the bottom of the question. Feel free to check it, thanks.

Collectives™ on Stack Overflow

annotate sum of annotated values in related model

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related