Count number of records by date in Django

Question

I'm using Django 1.1 with MySQL as the database.

I have a model similar to the following:

class Review(models.Model):
    venue = models.ForeignKey(Venue, db_index=True)
    review = models.TextField()  
    datetime_created = models.DateTimeField(default=datetime.now)

I'd like to query the database to get the total number of reviews for a venue grouped by day. The MySQL query would be:

SELECT DATE(datetime_created), count(id) 
FROM REVIEW 
WHERE venue_id = 2
GROUP BY DATE(datetime_created);

What is the best way to accomplish this in Django? I could just use Review.objects.filter(venue__pk=2) and parse the results in the view, but that doesn't seem right to me.

Answer 1

The lack of decent GROUP BY support is one of the points of contention I have with Django's ORM and why I describe it as merely "adequate". Drop to DB-API to do this query.

Answer 2

If you were storing a date field, you could use this:

Review.objects.filter(venue__pk = 2).values('date').annotate(event_count = Count('id'))

Because you're storing datetime, it's a little more complicated, but this should offer a good starting point. Check out the aggregation docs here

Answer 3

This should work (using the same MySQL specific function you used):

Review.objects.filter(venue__pk=2).extra({'date_created' : "date(datetime_created)"}).values('date_created').annotate(created_count=Count('id'))