CODEWITHSUNDEEP
pythondjango
mos
mos

Reputation: 347

How can I improve this query with Django ORM?

I have a Person model with name and city as attributes. I need a way to retrieve the amount of people by city. I need an output like this dictionary: {"NY": 23, "CA": 30, ...}

What I did was:

response = {}
for city in ["NY", "CA", ...]:
    response[city] = Person.objects.filter(city=city).count()

Here I have to provide a predefined list with cities and iterate over them. Does there exist any way to do this directly with Django ORM and avoid the loop?

Upvotes: 0

Views: 53

Answers (2)

vishes_shell
vishes_shell

Reputation: 23554

This will do

response = dict(Person.objects.values('city').\
                       annotate(count=Count('city')).\
                       values_list('city', 'count'))

UPDATE

As OP correctly noted, you can omit .values('city'):

response = dict(Person.objects.annotate(count=Count('city')).\
                       values_list('city', 'count'))

Upvotes: 1

vanadium23
vanadium23

Reputation: 3586

So to solve this problem, I basically go back to barebone SQL. You want something like this:

SELECT city, count(*) FROM person
WHERE city in (...)
GROUP BY 1

Then try to search, how to make aggregated params in django ORM:

from django.db.models import Count
Person.objects.filter(city__in=cities).annotate(count=Count('city'))

Upvotes: 1

Related Questions