You might do something like this...
db.session.query(Images)\
.join(VideoClip,
Images.video_clip_id VideoClip.id
)\
.join(RecordingSession,
RecordingSession.recording_id == VideoClip.id
)\
.filter(Images.video_clip_id = 'vcid',
RecordingSession.recording_id == 'foo'
)
If you need to join a given table more than once, you must create an alias for
it, then join and filter on the alias.
See https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.aliased
On Monday, October 28, 2019 at 7:48:04 AM UTC-4, Nitin Jain wrote:
>
> H i
>
> I have three tables in one to many relationships like below and Recording
> Session has many Videos which has many images . I would like to find out
> all the images of a particular recording session.
>
> As per understanding i need to join all the three tables and then filter.
>
> class RecordingSession(db.Model):
> __tablename__ = 'recording_session'
>
> id = db.Column(db.Integer, primary_key=True, autoincrement=True)
> recording_id = db.Column(db.String(50), unique= True , nullable=False)
>
>
> class VideoClip(db.Model):
> __tablename__ = 'video_clip'
>
> id = db.Column(db.Integer, primary_key=True , autoincrement=True)
> video_clip_id = db.Column(db.String(50), unique = True , primary_key=True)
>
> recording_id = db.Column(db.String(50),
> db.ForeignKey('recording_session.recording_id'))
>
> rsession = db.relationship('RecordingSession', backref='video_clip',
> lazy='joined')
>
>
> class Images(db.Model):
> __tablename__ = 'images'
>
> id = db.Column(db.Integer, primary_key=True, autoincrement=True)
> image_id = db.Column(db.String(50), primary_key=True, unique=True)
> video_clip_id = db.Column(db.String(50),
> db.ForeignKey('video_clip.video_clip_id',ondelete='CASCADE'))
> videoclips = db.relationship('VideoClip', backref='images', lazy='joined')
>
>
> I know how to join two tables like below but is it possible to join and
> filter using single query . I am using 1.3 version of sql alchemy.
>
>
> db.session.query(Images).join(VideoClip).filter(Images.video_clip_id = 'vcid')
>
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/a4bc4648-5d73-434f-99d1-54f980864d4a%40googlegroups.com.
