Fix Python – SqlAlchemy – Filtering by Relationship Attribute


Asked By – user1105851

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(''), index=True)
    mother = relationship('Patient', primaryjoin='', remote_side='', uselist=False)
    phenoscore = Column(Float)

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)

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

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.

Now we will see solution for issue: SqlAlchemy – Filtering by Relationship Attribute


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

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

or join (usually faster):

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

This question is answered By – Denis Otkidach

This answer is collected from stackoverflow and reviewed by FixPython community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0