Django Grouping Query
- by Matt
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
