python - How to efficiently aggregate across multiple models in Django? -

python - How to efficiently aggregate across multiple models in Django? -

i have complicated situation bear me. have 4 models:

maincategory category (foreign key main) cat (m2m category) dog (m2m category)

my general json output this:

[ {name: 'coat_type', cat_count: 3, dog_count: 5, total_count: 8, sub_cats: [ {name: 'furry', cat_count: 1, dog_count: 3, total_count: 4}, {name: 'shaggy', cat_count: 2, dog_count: 2, total_count: 4} ], {name: 'facial_features', ... } ]

so here set of queries, don't think efficient:

output = [] main_cat in maincategory.objects.all(): ... sub_cats = category.objects.filter(main_category=main_cat) counts = sub_cats.annotate( cat_count = count('cat', distinct=true), dog_count = count('dog', distinct=true), #how aggregate total count here? #total_count = cat_count + dog_count ).values('name', 'ip_count', 'doc_count', 'total_count') main_cat_dict = { 'name': main_cat.name, 'sub_cats': counts, ... } output.append(main_cat_dict)

i think sub-category aggregation okay, problem still have loop through each maincategory. i'm not sure how count total within each sub-category.

thanks in advance!

you can do:

sub_cats = category.objects.filter(main_category__in=maincategory.objects.all())

and annotations , values phone call did before. of course of study can first filter on maincategory model exclude main categories.

it not possible (at to the lowest degree not standard django orm) annotate based on other annotations. easier post-processing in python total count each entry:

for d in sub_cats: d['total_count'] = d['cat_count'] + d['dog_count']

python django