CODEWITHSUNDEEP
pythondjangoannotate
seuling
seuling

Reputation: 2956

django annotate related fields at once with Count()

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.

Upvotes: 0

Views: 1573

Answers (1)

user8060120
user8060120

Reputation:

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

Upvotes: 1

Related Questions