CODEWITHSUNDEEP
pythondjangodjango-querysetdjango-orm
Emile
Emile

Reputation: 3484

Django: Filtering a queryset then count

I'm trying to limit the number of queries I perform on a page. The queryset returns the objects created within the last 24 hours. I then want to filter that queryset to count the objects based upon a field.

Example:

cars = self.get_queryset()
volvos_count = cars.filter(brand="Volvo").count()
mercs_count = cars.filter(brand="Merc").count()

With an increasing number of brands (in this example), the number of queries grows linearly with the number of brands that must be queried.

How can you make a single query for the cars that returns a dict of all of the unique values for brand and the number of instances within the queryset?

Result:

{'volvos': 4, 'mercs': 50, ...}

Thanks!

EDIT:

Of the comments so far, they have been close but not quite on the mark. Using a values_list('brand', flat=True) will return the brands. From there you can use 

 from collections import Counter

To return the totals. It would be great if there is a way to do this from a single query, but maybe it isn't possible.

Upvotes: 1

Views: 4655

Answers (2)

user7283402
user7283402

Reputation: 1

SELECT brand, COUNT(*) as total 
FROM cars 
GROUP BY brand 
ORDER BY total DESC

Equivalent:

cars.objects.all().values('brand').annotate(total=Count('brand')).order_by('total')

Upvotes: 0

Daniel Roseman
Daniel Roseman

Reputation: 600041

To generate a count for each distinct brand, you use values in conjunction with annotate.

totals = cars.values('brand').annotate(Count('brand'))

This gives you a queryset, each of whose elements is a dictionary with brand and brand__count. You can convert that directly into a dict with the format you want:

{item['brand']: item['brand__count'] for item in totals}

Upvotes: 4

Related Questions