Flask SQLAlchemy filter records from foreign key relationship

flask, python, sqlalchemy

Solution

`Scenario.hints` is a query, so you'll need to use a join to perform this kind of filtering.

>>> scenarios = Scenario.query.join(Hint).filter(Hint.release_time < time.time())
>>> scenarios.first()
>>> <Scenario(scenario_name='hi', scenario_text='world', hints='SELECT hints.hint_id AS hints_hint_id, hints.scenario_id AS hints_scenario_id, hints.hint AS hints_hint, hints.release_time AS hints_release_time FROM hints WHERE :param_1 = hints.scenario_id')>

See the query docs and the ORM tutorial for more details.

Problem

I have 2 models: ``` class Scenario(db.Model): __tablename__ = 'scenarios' scenario_id = db.Column(db.Integer, primary_key=True) scenario_name = db.Column(db.String(120)) scenario_text = db.Column(db.Text) hints = db.relationship('Hint', backref='scenario', lazy='dynamic') def __init__(self, scenario_name, scenario_text): self.scenario_name = scenario_name self.scenario_text = scenario_text def __repr__(self): return "<Scenario(scenario_name='%s', scenario_text='%s', hints='%s')>" % self.scenario_name, self.scenario_text, self.hints class Hint(db.Model): __tablename__ = 'hints' hint_id = db.Column(db.Integer, primary_key=True) scenario_id = db.Column(db.Integer, db.ForeignKey('scenarios.scenario_id')) hint = db.Column(db.Text) release_time = db.Column(db.Integer) def __init__(self, scenario_id, hint, release_time): self.scenario_id = scenario_id self.hint = hint self.release_time = release_time def __repr__(self): return "<Hint(scenario_id='%s', hint='%s', release_time='%s')>" % self.scenario_id, self.hint, self.release_time ``` I want to be able to get all the scenarios with their corresponding hints but only the hints that have a release_time less than the current time. I figured this would work: ``` scenarios = Scenario.query.filter(Scenario.hints.release_time < time.time()) ``` But I get this error: ``` AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Scenario.hints has an attribute 'release_time' ``` I just started playing around with Flask and SQLAlchemy. Any advice would be appreciated.

Original source