I need to make this Query using Django QuerySystem.
SELECT DATE(`date`), count(*)
FROM `maggie_item`
GROUP BY DATE(`date`) DESC
My model:
Item
- date = DateTime
- title = textfield
I would appreciate your help
-
relevant: docs.djangoproject.com/en/dev/topics/db/aggregation/…meder omuraliev– meder omuraliev2010-10-02 22:49:02 +00:00Commented Oct 2, 2010 at 22:49
-
also, stackoverflow.com/questions/629551/…meder omuraliev– meder omuraliev2010-10-02 22:49:08 +00:00Commented Oct 2, 2010 at 22:49
Add a comment
|
2 Answers
Say your model is Item. Then:
from django.db.models import Count
Item.objects.values('date').annotate(Count('id'))
To group by dates instead of datetimes:
Item.objects.extra(select = { 'date': "DATE(date)" }).values('date').annotate(Count('id'))
I've only done this on Postgresql, where the following works, so I assume the above will work for you.
Item.objects.extra(select={'date' : "date_trunc('day', date)"}).values('date').annotate(Count('id'))
1 Comment
Arturo Jamaica
This is almost correct but I need to group by DATE('date') (Dates instead of datetimes). For example: Item.objects.values('date').annotate(Count('id')).order_by('date_date')
I did not find anyway to cast and group on using Django Query System, So I use a cursor
from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT DATE(date), count(id) FROM maggie_item GROUP BY DATE(date) DESC")
for a in cursor:
print a