django aggregate or annotate

Question

This is a very stupid thing, I know, but I just don't seem to get the handle on Django aggregate and annotate functions.

I have a very simple set of models: Events, Areas and Types. An event has foreign keys pointing to Area and Type. I would simply like to have the number of forthcoming events for any area and the same for any type, i.e. Area1 - 5 forthcoming events, Area2 - 6, or Type1 - 34 events and so on.

I would like to avoid writing custom SQL, and the q operator if possible.

Have you read the [documentation](https://docs.djangoproject.com/en/dev/topics/db/aggregation/) and actually tried to implement some of the examples? — T I, Jan 05 '12 at 13:50
Please show your code, so we can help you with a concrete problem. — Marcin, Jan 05 '12 at 13:52

score 23 · Accepted Answer · edited Jan 19 '19 at 06:33

for a given area:

my_area = Area.objects.all()[0]
Event.objects.filter(area=my_area).count()

annotation

events = Event.objects.annotate(Count('area'))
for event in events:
    print event, event.area__count

or

events = Event.objects.annotate(count=Count('area'))
for event in events:
    print event, event.count

See the following docs:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#annotate

score 21 · Answer 2 · answered Jan 05 '12 at 13:58

If you just need the total number of events for a single area, you don't need either annotate or aggregate, a simple count will do:

Event.objects.filter(area=my_area).count()

If you want the count of events for multiple areas, you need annotate in conjunction with values:

Event.objects.values('area').annotate(Count('area'))

freethrow · Answer 3 · 2012-01-06T16:45:13.240

3

Thank you all very much. The problem I was having is documented in the last version, it is about the annotate and filter precedence.

areas = Area.objects.filter(event__in = eventQuery).annotate(num=Count('event'))

My error was in the fact that I was doing annotate first and filter second.

edited Jan 06 '12 at 16:45

answered Jan 06 '12 at 16:31

freethrow

1,068
3
20
34

3

For those looking for this in the docs... [Order of annotate and filter clauses](https://docs.djangoproject.com/en/stable/topics/db/aggregation/#order-of-annotate-and-filter-clauses) – alekosot Oct 26 '17 at 14:47

score 0 · Answer 4 · answered Sep 11 '19 at 08:56

Given the models Event and Area as:

from django.db.models import Count

class Area(models.Model):
    area_name = models.CharField(...)
    address = models.CharField(...)


class Event(models.Model):
    event_name = models.CharField(...)
    area = models.ForeignKey(Area,...)

In order to get the total number of events in each area, you can do:

area_query = Area.objects.filter(yourfilter)
total_event_per_area = Event.objects.filter(area__in=area_query).values('area').annotate(Count('id'))
print(total_event_per_area)
<QuerySet [{'area': 2, 'id__count': 2}, {'area': 4, 'id__count': 3}]>

django aggregate or annotate

4 Answers4

Given the models Event and Area as:

In order to get the total number of events in each area, you can do: