On Thu, Sep 26, 2019, at 12:53 PM, Nitin Jain wrote:
> Hi all
>
> I want to join multiple tables using sqlalchemy ORM package and really
> finding it very difficult.
>
> Using raw SQL query i am able to do successful query but not using sql
> alchemy .
>
> Please let me know if somebody can point out what mistake i am doing. I guess
> it is related to maintaining proper relationships.
>
>
> *Models:-*
>
> ***class AnnotationData(db.Model):***
> __tablename__ = 'annotationdata'
>
> id = db.Column(db.Integer, primary_key=True, autoincrement=True)
> annotation_id = db.Column(db.String(50), primary_key=True,
> autoincrement=False)
> image_id = db.Column(db.String(50), db.ForeignKey('images.image_id',
> ondelete='CASCADE'))
> catalog_id = db.Column(db.String(50), db.ForeignKey('code.code_id',
> ondelete='CASCADE'))
>
>
> * **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)
> videos = db.relationship('VideoClip', backref='recording_session',
> lazy='dynamic')
>
> 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'))
> video_images = db.relationship('Images', backref='video_clip',
> lazy='dynamic')
>
> * **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'))
> annotation_data = db.relationship('AnnotationData',
> backref='images',
> lazy='dynamic')
>
>
> *Working Raq SQL Query :-*
>
> SELECT recording_session.recording_id, video_clip.recording_id,
> video_clip.video_clip_id,
> Images.video_clip_id, Images.Image_id,
> annotationdata.image_id, annotationdata.annotation_id,
> annotationdata.catalog_id
> FROM (((recording_session
> INNER JOIN video_clip ON recording_session.recording_id =
> video_clip.recording_id)
> INNER JOIN Images ON Images.video_clip_id = video_clip.video_clip_id)
> INNER JOIN annotationdata ON annotationdata.Image_id = Images.Image_id)
so you are right-nesting your JOINs, which can be done, but the Query.join()
method by itself won't generate those, there are more special APIs that can
generate that. However, inner joins are associative so the parenthesization
isn't needed.
>
>
>
> *Not working SQL Alchemy query :-*
>
> query_ = db.session.query(RecordingSession)
> query_ = query_.join(VideoClip, RecordingSession.recording_id ==
> VideoClip.recording_id) \
> .join(Images, Images.video_clip_id == VideoClip.video_clip_id
> .join(Images, Images.video_clip_id == VideoClip.video_clip_id) \
> .join(AnnotationData, Images.image_id == AnnotationData.image_id)
the query appears to join to the Images entity twice, so I would remove that
second .join(Images) as you already have one of those. otherwise looks fine.
>
>
> Thanks
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/287c5172-0b57-4d59-b90c-758f05273055%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/287c5172-0b57-4d59-b90c-758f05273055%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/15d939fb-efcb-4b0f-80ad-316df4de0f2c%40www.fastmail.com.
