SQLAlchemy checkbox filters records from foreign key relationship

Question

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 get all the scripts with their respective prompts, but only prompts that have release time less than current time.

I decided 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 with Flask and SQLAlchemy. Any advice would be appreciated.

Answer 1

Scenario.hints is a request, so you will need to use a connection 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 docs and ORM tutorial for more details.