Django: Query using group by and count

2 comments

To group the results by a certain field and count the number of matches in each row, use values() to group and annotate(Count()) to count.

from django.db.models import Count

brands = Product.objects.get_everything_for_category(category)

# Make it group by manufacturers and count the number of products for each brand betwen $50 and $100
brands = brands.filter(price__gte = 50, price__lte = 100)

# http://docs.djangoproject.com/en/dev/topics/db/aggregation/#values
brands = brands.values('manufacturer').annotate(Count('manufacturer')).order_by('manufacturer')

To access the information within "brands":

{% for brand in brands %}
  <li>{{brand.manufacturer}} ({{brand.manufacturer__count}})</li>
{% endfor %}

Related Posts

2 comments:

  1. I tried your code but in the result there is only the 'manufacturer' and not its count (using Django 1.2.3)

    ReplyDelete
  2. I'm also using Django 1.2.3 and it seems to be working fine here.

    Just remember that it pulls the same number of items, but it adds a new element with "__count" to every result.

    ie. {{ item.annotatename__count }}

    ReplyDelete

Leave your thoughts ...
---
If you are having trouble with copy/pasting in comments, you need to sign in or click 'Preview'. For more information about this Firefox bug, see here.

 
Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog