CODEWITHSUNDEEP
djangodjango-orm
Alexandr Shaulskyi
Alexandr Shaulskyi

Reputation: 25

Count objects in queryset by value in a field. Django

Imagine I have a model which looks something like following:

class Car(models.Model):

    TYPE_CHOICES = [
        (1: 'Hatchback')
        (2: 'Saloon')

    type = models.CharField(choices=TYPE_CHOICES, ...)

    color = models.CharField()

    owner = models.ForeignKey(User, ...)

And I want to count objects by specific values. Say black saloons owned by Johns or white hatchbacks owned by Matts.

The best what I came up so far is:

Car.objects.annotate(
    black_saloons_owned_by_Johns=Count(
        'type',
        filter=(
            Q(type=2) &
            Q(owner__first_name='John')
        )
    ),
    white_hatchbacks_owned_by_Matts=Count(
        'type',
        filter=(
            Q(type=1) &
            Q(owner__first_name='Matt')
        )
    )
).aggregate(
    aggregated_black_saloons_owned_by_Johns=Sum(
        'black_saloons_owned_by_Johns'
    ),
    aggregated_white_hatchbacks_owned_by_Matts=Sum(
        'white_hatchbacks_owned_by_Matts'
    )
)

Is there a better way to get the desired result? Thanks.

Update.

As I said, I need to perform multiple lookups in a single query. The query I used had just one example. I updated it. Should have explicitly point it out. Sorry.

Upvotes: 1

Views: 1536

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476729

We can filter the queryset, and then use .count() [Django-doc]:

Car.objects.filter(type=2, owner__first_name='John').count()

or if you need to perform multiple lookups, you can use .aggregate(..) directly:

You can Count the Car objects directly with:

Car.objects.aggregate(
    total_john=Count(
        'pk', filter=Q(type=2, owner__first_name='John')
    ),
    total_matts=Count(
        'pk', filter=Q(type=1, owner__first_name='Matt')
    )
)

this will then return a dictionary that has two keys: 'total_john' and 'total_matts', and these will contain the count of their number of Cars respectively.

Upvotes: 1

Related Questions