Django Grouping Query

Question

I have the following (simplified) models:

class Donation(models.Model):
    entry_date = models.DateTimeField()

class Category(models.Model):
    name = models.CharField()

class Item(models.Model):
    donation = models.ForeignKey(Donation)
    category = models.ForeignKey(Category)

I'm trying to display the total number of items, per category, grouped by the donation year.

I've tried this:

Donation.objects.extra(select={'year': "django_date_trunc('year', 
    %s.entry_date)" % Donation._meta.db_table}).values('year', 
    'item__category__name').annotate(items=Sum('item__quantity'))

But I get a Field Error on item__category__name.

I've also tried:

Item.objects.extra(select={"year": "django_date_trunc('year', 
    entry_date)"}, tables=["donations_donation"]).values("year", 
    "category__name").annotate(items=Sum("quantity")).order_by()

Which generally gets me what I want, but the item quantity count is multiplied by the number of donation records.

Any ideas? Basically I want to display this:

2010
  - Category 1: 10 items
  - Category 2: 17 items

2009
  - Category 1: 5 items
  - Category 3: 8 items

Answer 1

This other post looks like what you're looking for:

http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by

Depending on your Django version, you may or may not be able to use it though.

Answer 2

I realize you've probably already written your raw SQL, but the following came to mind when I saw the way you want to display your data:

If it's alright to do it at the template level you might be able to make strategic use of the regroup tag and length filter.

Regroup takes a "list of alike objects" so a queryset might work just fine, but the docs show a list of dictionaries, so I've used values here:

item_listing = Item.objects.values('category__name', 'donation__entry_date')
# use your favourite method to extract the year information into a key in item_listing
item_listing = ...

Now in the template, something like:

<ul>
{% for year_group in item_listing %}
    <li>{{ year_group.grouper }}
    <ul>
        {% regroup year_group.list by category__name as category_listing %}
        {% for category_group in category_listing %}
        <li>
        Category: {{ category_group.grouper }}
        Count: {{ category_group.list|length }}
        </li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>

I'm not sure if the regroup tag nests well like that (haven't tried it). Also, I have no idea how well regroup performs if you've got a lot of data, but then again, there's always caching...

If you decide to use this, make sure that you take note of the ordering gotcha mentioned in the regroup docs.