SqlAlchemy-按关系属性过滤 [英] SqlAlchemy - Filtering by Relationship Attribute

问题描述

我在SQLAlchemy方面没有太多经验，并且有一个我无法解决的问题.我尝试搜索，并且尝试了很多代码. 这是我的课程(简化为最重要的代码):

I don't have much experience with SQLAlchemy and I have a problem, which I can't solve. I tried searching and I tried a lot of code. This is my Class (reduced to the most significant code):

class Patient(Base):
    __tablename__ = 'patients'
    id = Column(Integer, primary_key=True, nullable=False)
    mother_id = Column(Integer, ForeignKey('patients.id'), index=True)
    mother = relationship('Patient', primaryjoin='Patient.id==Patient.mother_id', remote_side='Patient.id', uselist=False)
    phenoscore = Column(Float)

我想查询所有母亲的phenoscore为(例如)== 10

and I would like to query all patients, whose mother's phenoscore is (for example) == 10

如前所述，我尝试了很多代码，但我不明白.在我看来，合乎逻辑的解决方案是

As told, I tried a lot of code, but I don't get it. The logically solution, in my eyes, would be

patients = Patient.query.filter(Patient.mother.phenoscore == 10)

因为，输出时可以访问每个元素的.mother.phenoscore，但是此代码无法执行.

because, you can access .mother.phenoscore for each element when outputting but, this code doesn't do it.

是否存在(直接)按关系的属性进行过滤的可能性(无需编写SQL语句或其他联接语句)，我需要多次进行这种过滤.

Is there a (direct) possibility to filter by an attribute of a relationship (without writing the SQL Statement, or an extra join-statement), I need this kind of filter more than one time.

即使没有简单的解决方案，我也很高兴得到所有答案.

Even if there is no easy solution, I am happy to get all answers.

推荐答案

使用方法

Use method has() of relationship (more readable):

patients = Patient.query.filter(Patient.mother.has(phenoscore=10))

或加入(通常更快):

patients = Patient.query.join(Patient.mother, aliased=True)\
                    .filter_by(phenoscore=10)

这篇关于SqlAlchemy-按关系属性过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！