96👍
✅
This should work (using the same MySQL specific function you used):
Review.objects.filter(venue__pk=2)
.extra({'date_created' : "date(datetime_created)"})
.values('date_created')
.annotate(created_count=Count('id'))
27👍
Now that Extra()
is being depreciated a more appropriate answer would use Trunc such as this accepted answer
Now the OP’s question would be answered as follows
from django.db.models.functions import TruncDay
Review.objects.all()
.annotate(date=TruncDay('datetime_created'))
.values("date")
.annotate(created_count=Count('id'))
.order_by("-date")
- [Django]-Django The 'image' attribute has no file associated with it
- [Django]-Python 3 list(dictionary.keys()) raises error. What am I doing wrong?
- [Django]-How do I do a not equal in Django queryset filtering?
17👍
Just for completeness, since extra() is aimed for deprecation, one could use this approach:
from django.db.models.expressions import DateTime
Review.objects.all().\
annotate(month=DateTime("timestamp", "month", pytz.timezone("Etc/UTC"))).\
values("month").\
annotate(created_count=Count('id')).\
order_by("-month")
It worked for me in django 1.8, both in sqlite and MySql databases.
- [Django]-What is the max size of 'max_length' in Django?
- [Django]-Select_related with reverse foreign keys
- [Django]-Django: show the count of related objects in admin list_display
14👍
If you were storing a date field, you could use this:
from django.db.models import Count
Review.objects.filter(venue__pk = 2)
.values('date').annotate(event_count = Count('id'))
Because you’re storing datetime, it’s a little more complicated, but this should offer a good starting point. Check out the aggregation docs here.
👤Zach
- [Django]-How to pass an array in Django to a template and use it with JavaScript
- [Django]-ImportError: No module named django.core.wsgi Apache + VirtualEnv + AWS + WSGI
- [Django]-Why don't my south migrations work?
3👍
Also you can define custom function:
from django.db.models.expressions import Func
# create custom sql function
class ExtractDateFunction(Func):
function = "DATE" # thats the name of function, the way it mapped to sql
# pass this function to annotate
Review.objects.filter(venue__pk=2)
.annotate(date_created=ExtractDateFunction("datetime_created"))
.values('date_created')
.annotate(created_count=Count('id'))
Just make sure that your DB engine supports DATE function
- [Django]-How to update an object from edit form in Django?
- [Django]-Create if doesn't exist
- [Django]-Django orm get latest for each group
Source:stackexchange.com