I have three models - Patient, Prescription and Medicine. A Patient can have zero or more Prescriptions and a Prescription can have zero or more Medicines. Medicine has a duration field indicating the days for which the medicine is to be taken.
If we have say, two Prescriptions with two Medicines each with durations of 15, 15 and 15, 30 respectively, then the maximum prescription duration for the 2 Prescriptions are 15 and 30 respectively. And the total medication duration for that Patient is 15 + 30 = 45.
Now I want to filter the Patient queryset to find all Patients whose total medication duration is at least a certain specified value. Following is the code (using Django 3) I am trying. But, taking the above example, the total medication duration I get is 30, not 45.
from django.db import models
from django.db.models import Subquery, OuterRef, Max, Sum
class Patient(models.Model):
name = models.CharField(max_length=300)
# ...
class Prescription(models.Model):
patient = models.ForeignKey(
Patient,
related_name="prescriptions"
on_delete=models.CASCADE
)
# ...
class Medicine(models.Model):
prescription = models.ForeignKey(
Prescription,
related_name="medicines"
on_delete=models.CASCADE
)
duration = models.PositiveIntegerField()
# ...
def get_patients_with(minimum_medication_duration=365):
patient_qs = (
Patient.objects
.annotate(total_medication_duration=Subquery(
Prescription.objects
.filter(patient=OuterRef('pk'))
.values('patient__pk')
.annotate(max_prescription_durations=Subquery(
Medicine.objects
.filter(prescription=OuterRef('pk'))
.values('prescription__pk')
.annotate(max_duration=Max('duration'))
.values('max_duration')
))
.annotate(total_medication_duration=Sum('max_prescription_durations'))
.values('total_medication_duration')
))
.filter(total_medication_duration__gte=minimum_medication_duration)
)
return patient_qs
What am I be missing?