Django: Count objects in a particular month

Question

I have this model:

class Person(models.Model):
   city = models.CharField(max_length=20, blank=True)
   added_date = models.DateField(default=datetime.date.today)

I want to create a template/view that has a table of months and the number of people added that month (ie, 5 in january, 10 in february, 8 in march, etc.). I have a similar table for all the people from a each city using:

cities = Patient.objects.values('city').annotate(city_count=Count('city')).order_by('-city_count')

I don't know how to do that for my months table. I could filter for a particular month, then count all. But then I'd need to run that through a loop over every month, which would be multiple database hits. I don't want to do that.

Is there a way to do this without writing sql and just using django's api?

Answer 1

The fact that most DBs have such a perfect way of doing this with a GROUP BY query that has no analog in Django AFAIK would lead me to drop into SQL to do this. I did a google search for "django sql" and turned up this post by Doug Hellman on this exact problem: http://blog.doughellmann.com/2007/12/using-raw-sql-in-django.html. I would use that as a starting point for getting your month counts into Django.