如何统计Django多选字段中各选项的用户选择次数

我明白你遇到的问题了：用MultiSelectField存储用户选择的多种疾病，想要统计每种疾病的被选次数用于图表展示，但最初的查询逻辑把一组疾病当成整体分组，根本拆不开单个疾病的计数。别担心，咱们来搞定它！

先看你的模型代码（models.py） #

这是你定义的医疗记录模型，核心是用MultiSelectField支持多疾病选择：

from django.db import models
from django.utils import timezone
from django.contrib.auth.models import User
from multiselectfield import MultiSelectField

class MedicalHistory(models.Model):
    Anxiety = 'Anxiety'
    Arthritis = 'Arthritis'
    Asthma = 'Asthma'
    Anemia = 'Anemia'
    Cancer = 'Cancer'
    Corona_virus = 'Corona_virus'
    Diabetes = 'Diabetes'
    Ebola = 'Ebola'
    HIV = 'HIV'
    ILLNESS_CHOICES = (
        (Anxiety, "Anxiety"),
        (Arthritis, "Arthritis"),
        (Asthma, "Asthma"),
        (Anemia, "Anemia"),
        (Cancer, "Cancer"),
        (Corona_virus, "Corona_virus"),
        (Diabetes, "Diabetes"),
        (Ebola, "Ebola"),
        (HIV, "HIV"),
    )
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    illness = MultiSelectField(choices=ILLNESS_CHOICES, max_length=50)
    symptoms = models.CharField(max_length=100)
    additional_info = models.CharField(max_length=100)
    disability = models.BooleanField(default=False)
    medications = models.BooleanField(default=False)
    created_at = models.DateTimeField(default=timezone.now)

    def __str__(self):
        return f'{self.user.username} Medical History'

你最初踩坑的视图代码（views.py） #

你一开始尝试用values+annotate分组统计，但结果是把用户选择的一组疾病当作一个条目，完全不是你要的单疾病计数：

def pie_chart(request):
    labels = []
    data = []
    queryset = MedicalHistory.objects.values('illness').annotate(count=Sum('user')).order_by('-count')
    for entry in queryset:
        labels.append(entry['illness'])
        data.append(entry['count'])
    return JsonResponse(data={
        'labels': labels,
        'data': data,
    })

得到的查询结果是这样的，完全没法用来做单疾病统计：

<QuerySet [{'illness': ['Asthma', 'Diabetes', 'Ebola'], 'count': 3}, {'illness': ['Anemia', 'Covid-19'], 'count': 2}]>

正确的解决方案 #

咱们换个思路：先初始化一个包含所有疾病的计数字典，默认都设为0；然后遍历所有医疗记录，把每个用户选的疾病列表拆开来，逐个更新对应疾病的计数。这样就能准确得到每种疾病的被选次数了！

修改后的views.py代码：

def pie_chart(request):
    # 先给所有疾病初始化计数为0
    count = {
        'Anxiety': 0, 'Arthritis': 0, 'Asthma': 0, 
        'Anemia': 0, 'Cancer': 0, 'Corona_virus': 0, 
        'Diabetes': 0, 'Ebola': 0, 'HIV': 0 
    }
    # 获取所有用户的疾病选择记录
    queryset = MedicalHistory.objects.values('illness')
    # 遍历每条记录，拆分疾病列表更新计数
    for entry in queryset:
        for illness in entry['illness']:
            count[illness] += 1
    # 把字典的键（疾病名）和值（计数）分别提取成标签和数据
    labels = list(count.keys())
    data = list(count.values())
    # 把数据传给模板渲染
    return render(request, 'chart.html', {
        'labels': labels,
        'data': data,
    })

你的模板代码（chart.html） #

模板部分不用改太多，直接用传过来的labels和data就能生成图表：

{% block content %}
{% include 'sidebar.html' %}
<div id="container" style="width: 85%;">
    <canvas id="myChart"></canvas>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js@2.9.3/dist/Chart.min.js"></script>
<script>
var ctx = document.getElementById('myChart');
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: {{ labels|safe }},
        datasets: [{
            label: 'Overview of Medical History',
            data: {{ data|safe }},
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255, 99, 132, 1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero: true
                }
            }]
        }
    }
});
</script>
</div>
<!-- /#page-content-wrapper -->
</div>
<!-- /#wrapper -->
{% endblock %}

这样改完之后，就能准确统计每种疾病的被选次数，完美适配你的图表展示需求啦！

内容的提问来源于stack exchange，提问作者Bruce