Count objects in queryset by value in a field. Django

Question

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.

willeM_ Van Onsem · Accepted Answer · 2021-09-07 20:05:59Z

1

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.

edited Sep 7, 2021 at 20:05

answered Sep 7, 2021 at 19:57

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

4 Comments

joshlsullivan Over a year ago

Note that Q lookup is mostly used for "or" lookups. The comma in this answer stands for "and" - type=2 AND owner__first_name='John'

willeM_ Van Onsem Over a year ago

@joshlsullivan: well the filter in the question uses & which means that it is the conjunction of two conditions, not the disjunction.

willeM_ Van Onsem Over a year ago

@joshlsullivan: it would also conflict with the question text, since we want to count the number of saloons owned by a person named John, not the number of cars of type 2 or owned by John.

joshlsullivan Over a year ago

That's what I meant.

Collectives™ on Stack Overflow

Count objects in queryset by value in a field. Django

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related