当 TruncWeek 的一周在 Django 查询中没有结果时,如何添加 0?



我想每周查询问题的组计数。

query1 = MyModel.object.filter(issue_creator__in=group.user_set.all()).
annotate(week=TruncWeek('issue_creat_date')).values('week').annotate(count=Count('id')).order_by('week'))

查询结果正常。查询集结果:

[
{'week': datetime.datetime(2022, 1, 3, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 9}, 
{'week': datetime.datetime(2022, 1, 10, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 12}, 
{'week': datetime.datetime(2022, 1, 17, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 10}, 
{'week': datetime.datetime(2022, 2, 7, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 1}, 
{'week': datetime.datetime(2022, 2, 14, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 6}, 
{'week': datetime.datetime(2022, 2, 21, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 11}, 
{'week': datetime.datetime(2022, 2, 28, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 1}
]

但20220101-20220301有9周:

[
datetime.datetime(2022, 1, 3, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 1, 10, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 1, 17, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>),
datetime.datetime(2022, 1, 24, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 1, 31, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 2, 7, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 2, 14, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 2, 21, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 
datetime.datetime(2022, 2, 28, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>)
] 

当那周没有结果时,我想加零作为这个结果:

[
{'week': datetime.datetime(2022, 1, 3, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 9}, 
{'week': datetime.datetime(2022, 1, 10, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 12}, 
{'week': datetime.datetime(2022, 1, 17, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 10}, 
{'week': datetime.datetime(2022, 1, 24, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 0}, 
{'week': datetime.datetime(2022, 1, 31, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 0}, 
{'week': datetime.datetime(2022, 2, 7, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 1}, 
{'week': datetime.datetime(2022, 2, 14, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 6}, 
{'week': datetime.datetime(2022, 2, 21, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 11}, 
{'week': datetime.datetime(2022, 2, 28, 0, 0, tzinfo=<DstTzInfo 'Europe/Stockholm' CEST+2:00:00 DST>), 'count': 1}
]

如何编写正确的查询集?

谢谢。

None值Coalesce的Django方法。
from django.db.models.functions import Coalesce
query1 = MyModel.object.filter(issue_creator__in=group.user_set.all()).
annotate(week=TruncWeek('issue_creat_date')).values('week').annotate(count=Count('id')).order_by('week'))

最新更新