CODEWITHSUNDEEP
pythondjango
Roba
Roba

Reputation: 688

Django ORM: filter primary model based on chronological fields from related model

Let us presume that we have the following models:

class Patient(models.Model)
    name = models.CharField()
    # other fields following

class MedicalFile(model.Model)
    patient = models.ForeignKey(Patient, related_name='files')
    date = models.DateField()
    diagnostic = models.CharField()

We want to build a viewset for patients where we want to filter the Patient records based on their last available/valid diagnostic.

I don't know how to solve this without using raw SQLs. Is there an optimal way to build this statement using only Django query set syntax ?

How did I solve this ?

I'm sure this isn't nice, but it's optimal for filtering large datasets.

The idea is to use a view implemented at the database layer, where we will query all the patients with their associated latest medical file, than to map a Django entity to that view. Of corse, we will make the new model as un-managed.

Why to go such a long way ? Because on the new model we could use "re-usable" Django query syntax. Of corse, the view from the database isn't re-usable and has to be re-created for each db backend solution.

Considering Postgres, this would be the view definition:

SELECT
    p.*,
    f.*
FROM Person p
    LEFT JOIN (
        SELECT
            *,
            max(date) OVER (PARTITION BY person_id) AS latest_date
        FROM MedicalFile
    ) AS mf ON mf.person_id = p.person_id
WHERE
    mf.latest_date IS NULL OR mf.latest_date = mf.date

Then we could create the associated model like this

class LatestMedicalFile(models.Model):
    patient = models.OneToOneField(Patient, related_name="latest_file")
    date = models.DateField()
    diagnostic = models.CharField()

    class Meta:
        managed = False
        db_table = '<your view name here>'

Finally, our query can be written like this:

Patient.objects.filter(latest_file__diagnostic='flu')

This is not intuitive, nor clean, in my opinion. Any ideas ?

Upvotes: 3

Views: 146

Answers (1)

&#193;rni St. Steinunnarson
&#193;rni St. Steinunnarson

Reputation: 1610

If you are interested in seeing the files:

MedicalFile.objects.annotate(
    maxdate=Max(
        'patient__files__date'
        )).filter(
            maxdate=F('date'),
            diagnosis="flu").select_related(
                'patient'
            )

If you want the patients:

Patient.objects.annotate(
    maxdate=Max(
        'files__date'
        )).filter(
            maxdate=F('files__date'),
            files__diagnosis="flu"))

Great thanks to Roba, collaborative asking/answering is my favourite type of SO user.

Upvotes: 2

Related Questions