Django: Filtering a queryset then count

Question

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.

Daniel Roseman · Accepted Answer · 2017-01-23 19:17:30Z

4

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}

answered Jan 23, 2017 at 19:17

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

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

2 Comments

Emile Over a year ago

That is close, it seems like one way to do this is just queryset.values_list('brand', flat=True) and then use Counter on the result. That will return the value & count that I was looking for.

Shadow Over a year ago

Using Counter would indeed work - but would get really slow once you started getting a significant amount of results. That sort of work is much better suited to the database.

Jann · Accepted Answer · 2017-01-23 21:49:49Z

0

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

edited Jan 23, 2017 at 21:49

Jann

1,8294 gold badges22 silver badges39 bronze badges

answered Jan 23, 2017 at 19:16

user7283402

11 bronze badge

Collectives™ on Stack Overflow

Django: Filtering a queryset then count

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related