Django aggregate count of records per day

Question

I've got a django app that is doing some logging. My model looks like this:

class MessageLog(models.Model):
    logtime = models.DateTimeField(auto_now_add=True)
    user = models.CharField(max_length=50)
    message = models.CharField(max_length=512)

What a want to do is get the average number of messages logged per day of the week so that I can see which days are the most active. I've managed to write a query that pulls the total number of messages per day which is:

for i in range(1, 8):
    MessageLog.objects.filter(logtime__week_day=i).count()

But I'm having trouble calculating the average in a query. What I have right now is:

for i in range(1, 8):
    MessageLog.objects.filter(logtime__week_day=i).annotate(num_msgs=Count('id')).aggregate(Avg('num_msgs'))

For some reason this is returning 1.0 for every day though. I looked at the SQL it is generating and it is:

SELECT AVG(num_msgs) FROM (
SELECT 
`myapp_messagelog`.`id` AS `id`, `myapp_messagelog`.`logtime` AS `logtime`, 
`myapp_messagelog`.`user` AS `user`, `myapp_messagelog`.`message` AS `message`, 
COUNT(`myapp_messagelog`.`id`) AS `num_msgs` 
FROM `myapp_messagelog` 
WHERE DAYOFWEEK(`myapp_messagelog`.`logtime`) = 1 
GROUP BY `myapp_messagelog`.`id` ORDER BY NULL
) subquery

I think the problem might be coming from the GROUP BY id but I'm not really sure. Anyone have any ideas or suggestions? Thanks in advance!

Answer 1

I do something similar with a datetime field, but annotating over extra values does work for me. I have a Record model with a datetime field "created_at" and a "my_value" field I want to get the average for.

from django.db.models import Avg

qs = Record.objects.extra({'created_day':"date(created_at)"}).\
    values('created_day').\
    annotate(count=Avg('my_value'))

The above will group by the day of the datetime value in "created_at" field.

Answer 2

queryset.extra(select={'day': 'date(logtime)'}).values('day').order_by('-day').annotate(Count('id'))