CODEWITHSUNDEEP
pythonfiltersqlalchemyforeign-keys
user1105851
user1105851

Reputation: 1513

SqlAlchemy - Filtering by Relationship Attribute

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.

Upvotes: 151

Views: 144386

Answers (7)

Finch_Powers
Finch_Powers

Reputation: 3096

EDIT: This answer is old and based on sqlalchemy 1.x.

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)

Upvotes: 8

filiphasan
filiphasan

Reputation: 33

I use 'any()' function for add filter query on relationship column.

class ArticleModel(db.Model, BaseModel):
__tablename__ = "articles"

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
title = db.Column(db.String(120), nullable=False)
thumbnail = db.Column(db.String(240), nullable=True)
short_content = db.Column(db.String(400), nullable=False)
content = db.Column(db.String, nullable=False)
category_id = db.Column(db.Integer, db.ForeignKey("categories.id"), nullable=False)
category = db.relationship("CategoryModel", backref="articles", lazy=True)
views_count = db.Column(db.Integer, default=0, nullable=False)
comment_count = db.Column(db.Integer, default=0, nullable=False)
comments = db.relationship("CommentModel", backref="articles")
tags = db.relationship("ArticleTagModel", backref="articles", lazy=True)
seo_tags = db.Column(db.String(150), default="Software, Flask, Python, .Net Core, Web, Developer, JavaScript, React, Asp.Net, HTML5, CSS3, Web Development, Mobile, React Native", nullable=False)
seo_description = db.Column(db.String(150), default="", nullable=False)


class ArticleTagModel(db.Model, BaseModel):
__tablename__ = "article_tags"

id = db.Column(db.Integer, primary_key=True, autoincrement=True)
article_id = db.Column(db.Integer, db.ForeignKey("articles.id"), nullable=False)
tag_id = db.Column(db.Integer, db.ForeignKey("tags.id"), nullable=False)
tag = db.relationship("TagModel", backref="article_tags", lazy=True)

Use like this

articles = ArticleModel.query.filter(ArticleModel.tags.any(tag_id=tag_id)).all()

Upvotes: 0

Yaakov Bressler
Yaakov Bressler

Reputation: 12018

For those looking to accomplish this filter using a declarative base, you can use an association proxy:

from sqlalchemy.ext.associationproxy import association_proxy

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)

    """
    Access the associated object(s) through this proxy
    
    Note: Because the above relationship doesn't use a
      collection (uselist=False), the associated attribute
      will be a scalar. If the relationship does use a
      collection (uselist=True), the associated attribute 
      would then be a list (or other defined collection) of values.
    """
    mother_phenoscore = association_proxy('mother', 'phenoscore')

Instead of using has() on the relationship, you can query the child directly:

patients = Patient.query.filter(Patient.mother_phenoscore == 10)

Upvotes: 5

James
James

Reputation: 1851

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()

Upvotes: 6

Neel
Neel

Reputation: 21243

You have to query the relationsip with join

You will get the example from this Self-Referential Query Strategies

Upvotes: 12

Alexander Litvinenko
Alexander Litvinenko

Reputation: 319

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

Upvotes: 12

Denis Otkidach
Denis Otkidach

Reputation: 33190

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)

Upvotes: 247

Related Questions