SqlAlchemy - Filtering by Relationship Attribute
Solution 1
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)\
.filter_by(phenoscore=10)
Solution 2
You have to query the relationsip with join
You will get the example from this Self-Referential Query Strategies
Solution 3
Good news for you: I recently made package that gives you filtering/sorting with "magical" strings as in Django, so you can now write something like
Patient.where(mother___phenoscore=10)
It's a lot shorter, especially for complex filters, say,
Comment.where(post___public=True, post___user___name__like='Bi%')
Hope you will enjoy this package
https://github.com/absent1706/sqlalchemy-mixins#django-like-queries
Solution 4
I used it with sessions, but an alternate way where you can access the relationship field directly is
db_session.query(Patient).join(Patient.mother) \
.filter(Patient.mother.property.mapper.class_.phenoscore==10)
I have not tested it, but I guess this would also work
Patient.query.join(Patient.mother) \
.filter(Patient.mother.property.mapper.class_.phenoscore==10)
Solution 5
This is a more general answer on how to query relationships.
relationship(..., lazy='dynamic', ...)
This allows you to:
parent_obj.some_relationship.filter(ParentClass.some_attr==True).all()
user1105851
Updated on August 05, 2022Comments
-
user1105851 almost 2 years
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)
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.
-
user1105851 over 12 yearspatients = Patient.query.filter(Patient.mother.has(Patient.phenoscore==10))
-
Denis Otkidach over 12 years@user1105851
has()
supports both condition expression as unnamed argument andfilter_by
-style keyword arguments. The later seems more readable to me. -
aruisdante about 10 years@DenisOtkidach correct, but then it would be
phenoscore = 10
.filter_by
only takes equality keywords (since it's just doing **kwargs on them) -
Denis Otkidach about 10 years@aruisdante You are right, it was erroneous edit of the answer.
-
Boston Kenne over 5 yearsuse any instead: patients = Patient.query.filter(Patient.mother.any(phenoscore=10))
-
Ricky Levi about 2 yearsit gives me:
loaders cannot be used with many-to-one/one-to-one relationships and/or uselist=False