Django ORM：根据相关模型的时间序列过滤主模型

问题描述

让我们假设我们有以下模型：

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.

我不知道如何解决这个问题，而不使用原始的SQL 。使用Django查询集语法构建此语句是否有最佳方法？

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.

想法是使用在数据库层实现的视图，我们将在其中查询所有患者的相关最新医疗文件，而不是将Django实体映射到该视图。在corse中，我们将把新的模型作为未管理的。

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.

为什么要走这么长的路？因为在新模型上，我们可以使用可重用的Django查询语法。在corse中，数据库的视图不可重复使用，必须为每个db后端解决方案重新创建。

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.

考虑到Postgres，这将是视图定义：

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 ?

推荐答案

如果您有兴趣查看文件：

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'
            )

如果您想要患者：

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

非常感谢，协作询问/回答是我最喜欢的SO用户类型。

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

这篇关于Django ORM：根据相关模型的时间序列过滤主模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！