[sqlalchemy] How do I do this SQL query in SQLAlchemy?

[Jason Phillips]

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.