Ggtcf

I have the following SQLAlchemy mapped classes:

class ShowModel(db.Model):
 __tablename__ = 'shows'
 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(100)) 
 episodes = db.relationship('EpisodeModel', backref='episode', lazy='dynamic')

class EpisodeModel(db.Model):
 __tablename__ = 'episodes'
 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(200))
 show_id = db.Column(db.Integer, db.ForeignKey('shows.id'))
 info = db.relationship('InfoModel', backref='episode', lazy='dynamic')

class InfoModel(db.Model):
 __tablename__ = 'info'
 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(100))
 episode_id = db.Column(db.Integer, db.ForeignKey('episodes.id'))

I'm trying and failing to figure out how to perform a query that searches the info table for a specific name column value AND then return the shows and episodes table rows that are associated with it.

Using the following query allows me to return the specific info row that matches the filter_by(name=name) query

InfoModel.query.filter_by(name=name).all()))

But I am really struggling to figure out how to also get the values of the corresponding foreign key rows that have a relationship with the specific info row. Is there a proper way to do this with the join statement or something similar? Thank you very much for any help on this, as I'm still trying to get the hang of working with SQL databases, and databases in general.

Edit -

If, for example, I use the query InfoModel.query.filter_by(name="ShowName1").all())), my json() representation returns

 "name": "InfoName1",
 "id": 1,
 "episode_id": 1

But I'm also wanting to return the associated foreign table values so that my json() representation returns -

 "name": "ShowName1",
 "id": 1,
 "episodes": 
 
 "name": "EpisodeName1",
 "id": 1,
 "show_id": 1 
 "info": 
 
 "name": "InfoName1",
 "id": 1,
 "episode_id": 1
 
 ,

And I apologize for fumbling over my use of jargon here, making my question appear more complicated than it is.

Because you have lazy loading enabled, the joined tables will only be set when they are accessed. What you can do is force a join. Something like the following should work for you:

shows = session.query(ShowModel)
 .join(EpisodeModel)
 .join(InfoModel)
 .filter(ShowModel.name == "foo")
 .all()

You can also change your load configuration to be "eager", or any number of other options. I don't like to do this by default though, as it makes for accidentally expensive queries: https://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html