Aggregation for dates is a feature that has a trac ticket:
http://code.djangoproject.com/ticket/10302
One option would be to implement this (it's also listed for google SoC
2011 so maybe someone else will do it
http://code.djangoproject.com/wiki/SummerOfCode2011#Improveannotationandaggregation
).
Casey
On 03/24/2011 01:34 PM, James Taylor wrote:
Hi,
We're trying to do some reports of various things, and one of the
common issues we have is to do statistics on a per day or per-month basis - A
google search for various terms finds quite a few stackoverflow people trying
to do the same thing and not a lot of people are getting any success without
reverting to rawsql, which I don't really want to do.
I've made the smallest example I can here (on the end of this email), which is
a model file and a management command that goes with it. I've created two
linked (one to many) models, 'Article' with many 'Comments' - I want to know
how many comments have been made on each article per day (and preferably
efficiently without having to do a query per day with a specific filter).
Effectively, the code that I think *should work* is is:
query = Comment.objects.values( "article__title",
"made_at__year").annotate(comments=Count("pk"))
The error i get is:
" django.core.exceptions.FieldError: Cannot resolve keyword 'made_at__year' into
field. Choices are: article, author, id, made_at, message"
What are my options?
Best Regards
JT
----------
Smallest app demonstrating issue:
Models (stest/models.py)
========================
from django.db import models
# Create your models here.
class Article (models.Model):
title = models.TextField()
body = models.TextField()
class Comment (models.Model):
message = models.TextField()
author = models.TextField()
made_at = models.DateTimeField()
article = models.ForeignKey(Article)
Command (stest/management/commands/stats.py)
============================================
from django.core.management.base import BaseCommand, CommandError
from django.db.models import Count
from stest.models import Article, Comment
from datetime import datetime
class Command(BaseCommand):
args = ''
help = 'Testing stats run'
def handle(self, *args, **options):
print "Running Stats"
# Can use a filter to narrow down - but don't really mind in this
instance
# base_query = Comment.objects.filter(
# made_at__gte=date_from,
# made_at__lt=date_to
# )
# If I want to query per author per article (this works):
query = Comment.objects.values( "article__title",
"author").annotate(comments=Count("pk"))
for row in query:
print "%s : %s : %s comments" % (row["article__title"], row["author"],
row["comments"])
# if I want to query by day per article (this fails)
query = Comment.objects.values( "article__title",
"made_at__year").annotate(comments=Count("pk"))
for row in query:
print "%s : %s : %s comments" % (row["article__title"], row["made_at"],
row["comments"])
# django.core.exceptions.FieldError: Cannot resolve keyword
'made_at__year' into field. Choices are: article, author, id, made_at, message
--
You received this message because you are subscribed to the Google Groups "Django
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
