[Django]-Get highest count and corresponding value in Django query

by

8👍

✅

Try the following query:

from django.db.models import Count

Patient.objects.values('disease').order_by().annotate(Count('disease'))

If You have the following data in your Patient table, 

+---------+
| disease |
+---------+
| A       |
| B       |
| A       |
| C       |
| A       |
| A       |
| C       |
+---------+

The output will be :-

<QuerySet [{'disease': 'A', 'disease__count': 4}, {'disease': 'B','disease__count': 1}, {'disease': 'C', 'disease__count': 2}]>

There are many ways to find the max value from this output.

from django.db.models import Count
from django.db.models import Max

Patient.objects.values('disease').order_by().annotate(disease_count=Count('disease')).aggregate(maxval=Max('disease_count'))

Output will be :- {'maxval': 4}

OR

from django.db.models import Count

query_result = Patient.objects.values('disease').order_by().annotate(disease_count=Count('disease'))
maxval = max(query_result, key=lambda x:x['disease_count'])

Output will be :- {'disease': 'A', 'disease_count': 4}

Hope that helps!

👤aprasanth

4👍

I think the best approach would be to separate patient and disease in two different models like this:

class Patient(models.Model):
    ...

class Disease(models.Model):
    patients = models.ManyToManyField(Patient) # One disease can have n patients
    disease_name = models.CharField(# Customize this as you want)

then in your method you can do this (it can be a model method or an external method):

def your_method_name():
    Disease.objects.all().annotate(num_patients=Count('patients'))\
                         .order_by('num_patients')[:5]
👤Alessio Ferri

Leave a comment