注释(组)日期按月/年在Django [英] Annotate (group) dates by month/year in Django
问题描述
使用Django DateQuerySet
我从中提取
查询。项目
对象的相关年数组
Using the Django DateQuerySet
I'm pulling related years for item
objects from a Group
query.
>>> Group.objects.all().dates('item__date', 'year')
[datetime.date(1990, 1, 1), datetime.date(1991, 1, 1), ...(remaining elements truncated)...']
现在我想在这些日期上按不同年份执行计数。我以为这会工作:
Now I want to perform a count by distinct year on these dates. I thought this would work:
>>> Group.objects.all().dates('item__date', 'year').annotate(Count('year'))
FieldError: Cannot resolve keyword 'year' into field.
但是看起来我错过了一些东西。我如何解决这个问题?
But looks like I'm missing something. How can I fix this query?
我也试过这个查询:
>>> Group.objects.all().extra(select={'year': connections[Group.objects.db].ops.date_trunc_sql('year', 'app_item.date')})
ProgrammingError: missing FROM-clause entry for table "app_item" LINE 1: SELECT (DATE_TRUNC('year', app_item.date)) AS...
但是这也不行。
推荐答案
尝试以下几点:
from django.db.models import Count
Item.objects.all().\
extra(select={'year': "EXTRACT(year FROM date)"}).\
values('year').\
annotate(count_items=Count('date'))
您可能需要使用 item_instance._meta.fields
而不是在MySQL语句中手动指定date...
You might want to use item_instance._meta.fields
instead of manually specifying "date" in the MySQL statement there...
另外,请注意,我以项目
QuerySet而不是 Group
,为了简单起见。应该可以过滤 Item
QuerySet以获得所需的结果,或者使MySQL的额外
位更复杂。
Also, note that I started with Item
QuerySet instead of Group
, for the sake of simplicity. It should be possible to either filter the Item
QuerySet to get the desired result, or to make the extra
bit of MySQL more complicated.
编辑:
这可能会起作用,但是绝对测试出来的胆量,然后再依靠它:)
This might work, but I'd definitely test the guts out of it before relying on it :)
Group.objects.all().\
values('item__date').\
extra(select={'year': "EXTRACT(year FROM date)"}).\
values('year').\
annotate(count=Count('item__date'))
这篇关于注释(组)日期按月/年在Django的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!