how to return 5 top names per month, truncMonth-CodePudding

I've these models:

class City(models.Model):
    name = models.CharField(max_length=150)

class Hospital(models.Model):
    name = models.CharField(max_length=150)
    city = models.ForeignKey(City, on_delete=models.CASCADE, related_name="hospital")

class Patient(models.Model):
    name = models.CharField(max_length=150)
    city = models.ForeignKey(City, on_delete=models.CASCADE, related_name="patients")
    hospital = models.ForeignKey(Hospital, on_delete=models.CASCADE, related_name="patients", blank=True, null=True)
    date_visited = models.DateTimeField(auto_now_add=True)

i want to return top 5 hospitals per month which has most patients visited for example:

month : 10/1/2022 hospital A : 100 patient hospital B : 95 patient hospital C : 80 patient hospital D : 60 patient hospital E : 55 patient

and so on.

my views.py

from django.db.models.functions import TruncMonth
Patient.objects.annotate(month=TruncMonth('date_visited'))
#im not sure how to complete it!

I appreciate your helps

Thank you

CodePudding user response：

The following should work

Hospital.objects.annotate(number_of_patient=Count("patient", filter=Q(date_visited__month=desired_month))).order_by("-number_or_patient")[:5]

If you do not need the number of patient to be returned (you only need it for ordering) replace annotate by alias

CodePudding user response：

from django.db.models.functions import TruncMonth

top_hospitals = Patient.objects.annotate(month=TruncMonth('date_visited')).values('hospital__name', 'month').annotate(patient_count=Count('id')).order_by('month', '-patient_count').values('hospital__name', 'month', 'patient_count')[:5]