joining three tables using single query

[Nitin Jain]

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')

[Mike Bayer]

you can call join() as many times as you wish within a query, is there a SQL statement you are looking to generate?

--

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/718640ff-045f-48a9-b5a2-077088c96cb0%40googlegroups.com.

[Jonathan Vanasco]

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

[Jonathan Vanasco]

Whoops, my `==` got erased, it should read:

 

db.session.query(Images)\
    .join(VideoClip,
          Images.video_clip_id == VideoClip.id
          )\

I am explicitly providing the on-clause arguments, but that is not necessary for configured relationships ( see https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.join ) 

When using `join()` and configured relationships, if you are not filtering on that relationship you may want to use the `.options(contains_eager('relationship))`  construct (see https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#sqlalchemy.orm.contains_eager )