Django ORM Annotate Count Items and Order by Specific Order?

Question

我有一个图表，显示了不同地区的不同许可证类型以及每个地区的有效许可证数量。是否有一种方法可以将查询集中的项目按特定顺序排序以输出到图中?

这些是我的模型:

class Licence(models.Model):
status = models.CharField(choices=STATUS, max_length=1000)
number = models.CharField(unique=True, max_length=1000)
licence_type = models.ForeignKey(
"LicenceType", on_delete=models.SET_NULL, null=True
)
class LicenceType(models.Model):
region = models.ForeignKey(
"Region", on_delete=models.SET_NULL, null=True
)
class Region(models.Model):
slug = models.SlugField(primary_key=True)

这是我的观点:

def dashboard(request):
# total number of active licences across regions
active_licences = (
Licence.objects.values("licence_type", "licence_type__region")
.annotate(total=Count("id"))
.order_by("licence_type")
)
return render(request, "dashboard.html", "active_licences": active_licences)

是否有一种方法可以指定区域出现的顺序?例如，它们当前的顺序是(按pk)[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]，但我希望它们显示为[1, 3, 2, 4, 5, 6, 7, 8, 9, 10]。

Answer 1

我最终在视图中操作了来自查询集的数据，并将其作为元组列表返回给模板，如下所示:

def dashboard(request):
# total number of active licences across provinces
licences_by_province = list(
Licence.objects.values("licence_type__region")
.annotate(total=Count("id"))
.order_by("licence_type")
)
# save provinces and totals to a list
licence_list = []
for province in licences_by_province:
licence_list.append(tuple(province.values()))
# re-order list to include ON, AB, BC at the beginning
d = dict([('ON', 0), ('AB', 0), ('BC', 0)])
d.update(dict(licence_list))
active_licences = list(d.items())
return render(request, "dashboard.html", "active_licences": active_licences)

Answer 2

看来条件排序是你正在寻找的。

试题:

from django.db.models import Case, IntegerField, Value, When

your_order = ['ON', 'AB', 'BC', ... ]
Licence.objects.alias(
order_by_region=Case(
*[When(licence_type__region=item, then=Value(i) for i, item in enumerate(your_order))],
default=Value(len(your_order)),
output_field=models.IntegerField(),
)
).values('licence_type__region').annotate(total=Count('id')).order_by('order_by_region')

请看答案