peewee - peewee中的多个连接

Question

我被难住了。因此，我在 peewee 中定义了三个模型:

class Patient(BaseModel):
    patientIdx = IntegerField()
    gender = CharField()
    firstName = CharField()
    middleInit = CharField()
    lastName = CharField()
    provider = ForeignKeyField(User)
    MRN = CharField()
    class Meta:
        database = database

class Metrics(BaseModel):
    metricId = CharField( )
    metricDescription = CharField()
    ptListDescription = CharField()
    class Meta:
        database = database

class PatientMetric(BaseModel):
    metric = ForeignKeyField(Metrics)
    patient = ForeignKeyField(Patient)
    class Meta:
        database = database

我正在尝试使用 peewee 表达这个 mySQL 查询:

select m.metricId, p.id from
patient as p
join patientmetric pm on p.id = pm.patient_id
join metrics m on pm.metric_id = m.id
where provider_id = 91

基本上，只要给我一份患者列表和给定提供者的指标 ID。很简单，但我不知道如何让 peewee 做到这一点。我试过链接连接，但 peewee 无法解析外键。我试过以几种不同的方式使用 .join 和 .from_ 方法，例如:

Metrics.select()
        .from_(
            PatientMetric.select().join(Patient).where(Patient.provider == provider),
            Metrics)
        .where(Metrics.id == PatientMetric.metric)

在这种情况下，mySQL 提示需要为派生表创建别名。我想我找错了树，有一种在 peewee 中构建此查询的简单方法，但我很难过。谁能告诉我“正确”的方法？

Answer 1

query = (Patient
         .select(Metrics.metricId, Patient.id)
         .join(PatientMetric)
         .join(Metrics)
         .where(Patient.provider == 91)
         .tuples() # <-- since you just need the metric id and patient id
)