django annotate related fields at once with Count()

Question

My Profile model have two m2m fields in one model - region and country. And as you know, country has there own region with foreignkey.

I want to try counting profiles per region - not only include region but also in country__region.

i.e.) If some have only has Africa region, and other has only Congo country (with the region Africa), I want to filter them at one.

I try to solve it using annotate. I can find count of region individually like below

    profiles = Profile.objects.all()
    region_count = profiles.values('region').annotate(region_count=Count('region'))
    country_count = profiles.values('region').annotate(region_count=Count('country__region'))

But how can I count queryset with specific region, filtering with region and region__country at once? Is there any possible method?

Here's my profile / country model. The region model just has name field.

class Profile(models.Model):
    region = models.ManyToManyField(
        Region,
        verbose_name="Region(s) of interest",
        blank=True,
    )
    country = models.ManyToManyField(
        Country,
        related_name="country",
        verbose_name="Countries of interest",
        blank=True,
    )
    ...

class Country(models.Model):
    region = models.ForeignKey(
        Region,
        null=True,
        blank=True,
    )
    ...

Thanks for any help.

Summary

I want to count queryset with region and country__region at once with annotate.

is this works ? ( I don't really understand your problem ) region_count = profiles.values('region').annotate(Count('region'), Count('country__region')).distinct() — Alexis
– Alexis, Commented Jul 11, 2018 at 9:16
@AlexisdeCorbier That can't merge region and country__region. My problem detail - I want to get all profiles including region or country__region per region. i.e.) africa - profiles having africa region and having africa in country__region both. — seuling
– seuling, Commented Jul 11, 2018 at 9:23

user8060120 · Accepted Answer · 2018-07-11 09:49:34Z

1

you can try use conditional-expressions before count:

from django.db.models import Case, When, F, Count

Profile.objects.annotate(
    reg=Case(
        When(region__isnull=True, then=F('country__region')),
        default=F('region'))
    ).values('reg').annotate(region_count=Count('reg'))

answered Jul 11, 2018 at 9:49

user8060120

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

3 Comments

user8060120 Over a year ago

@seuling does it help you, or i wrong in understand your question.

seuling Over a year ago

I tried your answer, and it's good to know Case and When. Unfortunately, I guess it's not what I want to find. If use conditional expressions, one profile object is counted just one time. For example, if someone has Africa region and South Korea country, he/she should be counted in both Africa and Asia. But in case-when scenario, it's only counted for Africa.

seuling Over a year ago

Anyway, really thanks for your helpful answer. I'm trying to select query with conditional expression

Collectives™ on Stack Overflow

django annotate related fields at once with Count()

Summary

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Summary

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related