django count distinct values of multiple fields

Question

I've no idea on a efficient way to count "GOOD" or "BAD" in a single Feedback.(count from all fields, mean count how many good does this Feedback has).

My models.py is as

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)

ruddra · Accepted Answer · 2021-02-01 09:15:56Z

I don't think there is a very good way to calculate multiple fields. If you want to calculate one field, then you can use GROUP BY function:

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

But that won't work for multiple fields though. In that case, you can either add a property method like this:

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

Alternative solution

Now, if its possible to redesign the models like:

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)

Then you can simply query:

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

Collectives™ on Stack Overflow

django count distinct values of multiple fields

1 Answer 1

Alternative solution

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Alternative solution

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related