Django Grouping Query

Posted by Matt on Stack Overflow See other posts from Stack Overflow or by Matt
Published on 2010-05-14T01:03:21Z Indexed on 2010/05/14 1:14 UTC
Read the original article Hit count: 303

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about django

Related posts about python