SqlAlchemy - Filtering by Attribute Attribute

Question

I do not have much experience with SQLAlchemy, and I have a problem that I cannot solve. I tried to search and I tried a lot of code. This is my class (shortened 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) 

and I would like to interview all patients whose mother is a fenoscor (for example) == 10

As I said, I tried a lot of code, but I don’t understand. A logical decision, in my opinion, would be

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

because you can access .mother.phenoscore for each element in the output, but this code does not.

Is there a (direct) ability to filter by attribute of a relation (without writing an SQL statement or an additional join statement), I need a filter of this type more than once.

Even if there is no easy solution, I will be happy to receive all the answers.

Answer 1

Use the has() method of relations (more readable):

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

or join (usually faster):

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

Answer 2

You must request a relationship using a join

You will get an example from this self-link query strategy

Answer 3

Good news for you: I recently made a package that gives you filtering / sorting with "magic" strings like in Django , so you can now write something like

 Patient.where(mother___phenoscore=10) 

This is much shorter, especially for complex filters, say

 Comment.where(post___public=True, post___user___name__like='Bi%') 

Hope you enjoy this package

https://github.com/absent1706/sqlalchemy-mixins#django-like-queries

Answer 4

I used it with sessions, but an alternative way to directly access the relationship field is

 db_session.query(Patient).join(Patient.mother) \ .filter(Patient.mother.property.mapper.class_.phenoscore==10) 

I have not tested it, but I think it will work too

 Patient.query.join(Patient.mother) \ .filter(Patient.mother.property.mapper.class_.phenoscore==10)