Django aggregation and following foreign key relations

Question

Models:

class Item(models.Model):
    name = models.CharField(max_length=100)
    # More stuff.
class Sale(models.Model):
    sale_date = models.DateField(auto_now_add=True)
    item = models.ForeignKey(Item)
    # More stuff.

View:

class TodaySales(ListView):
    # Stuff in here.
    def get_queryset(self):
        sales_today = Sale.objects.filter(sale_date=date.today())
        return sales_today.values('item').annotate(Count('item'))

I get back something like this:

[{'item': 1, 'item__count': 2}, {'item': 2, 'item__count': 1}]

This is nice and all and but what I really want is to be able to get extra information about the item, such as its name, etc. But I can't seem to do this easily since all I get is the item ID, so I can't in my template do, for example, {{ item.name }}.

Is there some good way of doing this?

score 8 · Accepted Answer · answered Dec 12 '11 at 13:26

8

This will do. Try:

Item.objects.filter(
    sale__sale_date=date.today()
).annotate(
    count = Count('sale')
)

answered Dec 12 '11 at 13:26

Ilya

1,349
11
16

James Brady · Answer 2 · 2011-12-12T13:36:42.727

As you care about getting extra data from the Item, rather than the Sale, you could query on that class:

models.Item.objects.filter(sale__sale_date=date.today()).annotate(Count('id'))

That returns ORM objects, rather than values or a values_list: you'll also have a synthetic id__count attribute available with the sale count for that particular item.

Alternatively, if you know ahead of time exactly which fields you want, you can also name them explicitly in your values invocation, e.g.:

sales_today.values('item', 'item__name').annotate(Count('item'))

Django aggregation and following foreign key relations

2 Answers2

Linked