Django Grouping Query
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 am trying to display the total number of items, for each category, grouped by donation date.
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 am getting 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()
Generally, I get what I want, but the unit count is multiplied by the number of donation records.
Any ideas? Basically I want to show this:
2010 - Category 1: 10 items - Category 2: 17 items 2009 - Category 1: 5 items - Category 3: 8 items
a source to share
This other post looks like what you want:
Django equivalent for count and group
Depending on your version of Django, you may or may not be able to use it.
a source to share
I realize you've already written your raw SQL, but the following came to my mind when I saw how you want to display your data:
If it can be done at the template level, you can strategically use the regroup tag and length filter.
The rearrangement takes a "list of similar objects", so the query might work very well, but the docs show a list of dictionaries, so I used the 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 rearrangement tag is a good match (haven't tried it). Also, I have no idea how well the regrouping works if you have a lot of data, but then again, there is always caching ...
If you decide to use this, make sure you take into account the ordering information in the group documents.
a source to share