CODEWITHSUNDEEP
djangodjango-models
Arturo Jamaica
Arturo Jamaica

Reputation: 73

Help with Django Query

I need to make this Query using Django QuerySystem.

SELECT  DATE(`date`), count(*)
FROM `maggie_item`
GROUP BY DATE(`date`) DESC

My model:

Item

I would appreciate your help

Upvotes: 1

Views: 202

Answers (2)

ars
ars

Reputation: 123498

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

Upvotes: 3

Arturo Jamaica
Arturo Jamaica

Reputation: 73

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

Upvotes: 0

Related Questions