Django统计多个字段的不同值

Question

我不知道如何有效地计算单个反馈中的“好”或“坏”（从所有字段中计算，平均计算此反馈有多少好）

Mymodels.py如下所示

class Feedback(models.Model):
    ...
    class FeedbackOption(models.TextChoices):
        BAD = "BAD", "Bad"
        GOOD = "GOOD", "Good"
    ...
    comment = models.TextField()
    timestamp = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    overall_experience = models.CharField(max_length=32, choices=FeedbackOption.choices)
    doctor_checkup = models.CharField(max_length=32, choices=FeedbackOption.choices)
    staff_behavior = models.CharField(max_length=32, choices=FeedbackOption.choices)
    clinic_environment = models.CharField(max_length=32, choices=FeedbackOption.choices)

Answer 1

我不认为有一个很好的方法来计算多个字段。如果要计算一个字段，则可以使用GROUP BY函数：

Feedback.objects.values('overall_experience').annotate(feedback=Count('overall_experience').values('feedback')

但是，这对于多个领域来说是行不通的。在这种情况下，您可以添加如下属性方法：

class Feedback(models.Model):
    ...


    @property
    def good_count(self):
       counter = 0
       for f in ['overall_expereince', 'doctor_checkup', 'staff_behavior', 'clinic_environment']:
           if getattr(self, f) == 'GOOD':
                counter += 1
       return counter

    @property
    def bad_count(self):
       counter = 0
       for f in ['overall_expereince', 'doctor_checkup', 'staff_behavior', 'clinic_environment']:
           if getattr(self, f) == 'BAD':
                counter += 1
       return counter


 # usage
 for f in Feedback.objects.all():
     f.good_count
     f.bad_count

替代解决方案

现在，如果可能，可以重新设计模型，如：

class Feedback(models.Model):
   ...

class FeedbackScore(models.Model):
    feedback = models.ForeignKey(Feedback, on_delete=DO_NOTHING, related_name='scores')
    class FeedbackOption(models.TextChoices):
        BAD = "BAD", "Bad"
        GOOD = "GOOD", "Good"
    class FeedbackType(models.TextChoices):
        OVERALL = "OVERALL", "Overall Exp"
        DOCTOR = "DOCTOR", "Doctor Checkup"
        STAFF = "STAFF", "Staff Behavior"
    choice = models.CharField(max_length=32, choices=FeedbackOption.choices)
    feedback_type = models.CharField(max_length=32, choices=FeedbackType.choices)

然后，您可以简单地查询：

Feedback.objects.annotate(good=Count('scores', filter=Q(scores__choice='GOOD')), bad=Count('scores', filter=Q(scores__choice='BAD'))).values('good', 'bad')