Count number of records by date in Django

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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'))

Method 2

Now that Extra() is being depreciated a more appropriate answer would use Trunc such as this accepted answer

Now the OP’s question would be answered as follows

from django.db.models.functions import TruncDay

Review.objects.all()
    .annotate(date=TruncDay('datetime_created'))
    .values("date")
    .annotate(created_count=Count('id'))
    .order_by("-date")

Method 3

Just for completeness, since extra() is aimed for deprecation, one could use this approach:

from django.db.models.expressions import DateTime

Review.objects.all().
    annotate(month=DateTime("timestamp", "month", pytz.timezone("Etc/UTC"))).
    values("month").
    annotate(created_count=Count('id')).
    order_by("-month")

It worked for me in django 1.8, both in sqlite and MySql databases.

Method 4

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

from django.db.models import Count

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.

Method 5

Also you can define custom function:

from django.db.models.expressions import Func

# create custom sql function
class ExtractDateFunction(Func):
    function = "DATE" # thats the name of function, the way it mapped to sql

# pass this function to annotate
Review.objects.filter(venue__pk=2)
      .annotate(date_created=ExtractDateFunction("datetime_created"))
      .values('date_created')
      .annotate(created_count=Count('id'))

Just make sure that your DB engine supports DATE function

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0