this is a very basic series of joins which can be approached using the
techniques described in the ORM tutorial:
http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#querying-with-joins .
the parenthesization of the joined tables within subqueries is also not needed
and these tables can be joined directly to produce the same result.
On Jul 2, 2012, at 3:06 AM, Jason Phillips wrote:
> I have a MySQL database with the following structure and was hoping someone
> could help me convert the SQL query below to SQLAlchemy.
>
> Database structure:
>
> bottom:
> id
> name
> middle_id
>
> middle:
> id
> name
> top_id
>
> top:
> id
> name
>
> Here are my models:
>
> class Bottom(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(64))
> middle_id = db.Column(db.Integer, db.ForeignKey('middle.id'))
> middle = db.relationship('Middle',
> backref=db.backref('bottoms', lazy='dynamic'))
>
> class Middle(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(64))
> top_id = db.Column(db.Integer, db.ForeignKey('top.id'))
> top = db.relationship('Top',
> backref=db.backref('middles', lazy='dynamic'))
>
> class Top(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(64))
>
> Here's the SQL I want to convert to SQLAlchemy:
>
> SELECT
> b.*,
> m.*,
> t.*
> FROM bottom AS b
> LEFT JOIN (SELECT id, name, top_id from middle) AS m on m.id =
> b.middle_id
> LEFT JOIN (SELECT id, name FROM top) AS t on t.id = m.top_id
>
> Thank you in advance :).
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/JpDLJzBXBzUJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
