SQLAlchemy ORM search through multiple relationships for an attribute

Question

I know it's possible to filter an initial query by a relational attribute (see SqlAlchemy - Filtering by Relationship Attribute)

But what about after you've already retrieved the object?

Given:

class Parks(Base):
    __tablename__ = 'parks'

    id = Column(Integer, primary_key=True)
    description = Column(String, nullable=False, unique=True, index=True)
    state = Column(String(2))
    city = Column(String)
    zipcode = Column(String, index=True)

    alerts = relationship('Alerts', back_populates='park')


class Alerts(Base):
    __tablename__ = 'alerts'

    id = Column(Integer, primary_key=True)
    park_id = Column(Integer, ForeignKey('parks.id'))
    alert_type_id = Column(Integer, ForeignKey('alert_types.id'))
    alert_message = Column(String)

    park = relationship('Parks', back_populates="alerts")
    alert_type = relationship('AlertTypes', back_populates='alerts')


class AlertTypes(Base):
    __tablename__ = 'alert_types'

    id = Column(Integer, primary_key=True)
    alert_type_long_des = Column(String)

    alerts = relationship('Alerts', back_populates='alert_type')

I know I can do

park_closed_alert = session.query(AlertTypes)\
    .filter(AlertTypes.alert_type_long_des == 'Park Closed').first()

closed_parks = session.query(Parks)\
    .filter(Parks.alerts.any(alert_type = park_closed_alert )).all()

to get a list of ALL the closed parks...

but what if I've already retrieved a specific park, and I then want to check to see if it's closed? Or to see if maybe multiple alerts (relationships) exists for a specific criteria.

park_record_from_db = session.query(Parks).filter(Parks.longdes == 'City Park').first()

park_record_from_db.alerts will give me a list of alerts, and I can easily iterate through them... But since calling the .alerts relationship will query the DB again anyways, can I just pass in the filter?

jspcal · Accepted Answer · 2018-06-17T04:12:22.797

This can be done with dynamic relationship loaders. Specify lazy=dynamic when defining the relationship. The property will then behave as a Query object that can be filtered rather than loaded immediately.

alerts = relationship('Alerts', back_populates='park', lazy='dynamic')
[...]
filtered_alerts = park_record_from_db.alerts.filter( \
    Alerts.alert_type.in_((park_closed_alert, park_dogs_alert))).all()

Another option is query-enabled properties which allows more customization.

Of course, if you want to filter by relationship at the point where you retrieve the initial object, you can use the any operator:

park_record_from_db = session.query(Parks) \
    .filter(Parks.longdes == 'City Park') \
    .filter(Parks.alerts.any( \
        Alerts.alert_type.in_((park_closed_alert, park_dogs_alert)))) \
    .first()

SQLAlchemy ORM search through multiple relationships for an attribute

1 Answers1