Django list of users with count of events (appointments)

In my Django scheduling application in models.py I have a User class and an Appointment class. How can I display a table of users with the number of users and information on how many appointments the patient had in total in the html page. Table like: (number of patient/name of patient/number of appointments) and result like: (1/user1/3; 2/user2/5 ….)

models.py

class Pricelist(models.Model):
    service=models.CharField('Name of service', max_length=60)
    price=models.DecimalField(max_digits=6, decimal_places=2)
    valid=models.BooleanField("Valid", default=False)

    def __str__(self):
        return f"{self.service} = {self.price} EUR"

class Patient(models.Model):
    name=models.CharField('Name and Surname', max_length=60)
    date_of_birth=models.CharField('Date of Birth', max_length=30, blank=True)
    address=models.CharField("Address", max_length=300)
    date_of_application=models.DateField(default=timezone.now)
    is_active=models.BooleanField(default=False)
    description = models.TextField(blank=True)

    def __str__(self):
        return self.name

class Appointment(models.Model):
id = models.AutoField(primary_key=True)
user=models.ForeignKey(User, on_delete=models.CASCADE, null=True, blank=True, limit_choices_to={'active': True},)
appointment=models.ForeignKey(Pricelist, on_delete=models.CASCADE, null=True, blank=True)
day=models.DateField(default=date)
time_service = models.CharField(max_length=10, choices=TIME-CHOICE, default="16:00")
is_paid = models.BooleanField(default=False)

def __str__(self):
return f"{self.user.name} | dan:{self.day} | time:{self.time_service} | service:{self.service.price}"
    
    class Meta:
        db_table = "tblappointment"

i get information for every patient in view.py by id:

def patient_statistics(request, patient_id):
     patient = Patients.objects.get(pk=patient_id)
     appointments = patient.appointment_set.all()
     appointment_count = patient.appointment_set.all().count()
     total_price = terms.aggregate(sum=Sum('treatment__price'))['sum']
     return render(request, 'patients/patient_statistics.html', {"appointment_count":appointment_count, "patient":patient, 'terms':terms, 'total_price':total_price})

but I don’t know how to make a summary table of patients and the number of treatments. I am asking for help.

>Solution :

You can .annotate(…) ^[Django-doc] with:

from django.db.models import Count

Patient.objects.annotate(appointments=Count('appointment_set'))

The Patient objects that arise from this QuerySet will have an extra attribute .appointments, so in your view, you use:

from django.db.models import Count


def patient_statistics(request):
    patients = Patients.objects.annotate(appointments=Count('appointment_set'))
    return render(
        request, 'patients/patient_statistics.html', {'patients': patients}
    )

and you thus can render this with:

{% for patient in patients %}
  {{ patient }}: {{ patient.appointments }}<br>
{% endfor %}