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.
推荐答案
使用方法 has()
关系(更易读):
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屋!