django用动态列名注释 [英] django annotate with dynamic column name
问题描述
我在Django应用中有一个模型,其结构如下:
I have a model in django app, with the following structure:
class items(models.Model):
name = models.CharField(max_length=50)
location = models.CharField(max_length=3)
我想为每个名称/项目的每个位置的计数创建一个数据透视表,我设法按照以下步骤进行操作:
I wanted to create a pivot table for the count of each location per each name/item, which I managed to do as per the following:
queryset_res = items.objects.values('name')\
.annotate(NYC=Sum(Case(When(location='NYC', then=1),default=Value('0'),output_field=IntegerField())))\
.annotate(LND=Sum(Case(When(location='LND', then=1),default=Value('0'),output_field=IntegerField())))\
.annotate(ASM=Sum(Case(When(location='ASM', then=1),default=Value('0'),output_field=IntegerField())))\
.annotate(Total=Count('location'))\
.values('name', 'NYC', 'LSA','Total')\
.order_by('-Total')
这给了我多少
我的问题是如何使位置动态化,所以如果添加了新位置,我就没有了回来并再次更改代码!来自列表或来自模型数据本身
my question is how can I make the location dynamic, and so if new locations where added I don't have come back and change the code again! either from a list or from the model data itself
非常感谢
AB
Many Thanks AB
推荐答案
您可以将动态参数与 * [1、2、3]
, ** {'key':'value '}
在python中。
You can bind dynamic parameter with *[1, 2, 3]
, **{'key': 'value'}
in python.
from django.db.models import Case, Count, Sum, IntegerField, Value, When
def get_annotation(key):
return {
key: Sum(
Case(
When(location=key, then=Value(1)),
default=Value(0),
output_field=IntegerField(),
),
),
}
queryset_res = items.objects.values('name')
location_list = ['NYC', 'LSA', 'ASM', ...etc]
for key in location_list:
queryset_res = queryset_res.annotate(**get_annotation(key))
queryset_res = (
queryset_res.annotate(Total=Count("location"))
.values("name", "Total", *location_list)
.order_by("-Total")
)
现在,您只需更改以下内容即可实现一组查询 location_list
。
Now you can implement a set of queries simply by changing location_list
.
这篇关于django用动态列名注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!