Help with Django Query

Question

Hi Guys:

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

Answer 1

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

Answer 2

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