SQLAlchemy - How to MySQL JOIN

Question

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
WHERE b.name LIKE '%query%' OR m.name LIKE '%query%' OR t.name LIKE '%query%'

Each entry in the table bottom is related to middle, and each entry in middle is related to top. I want to query the database to find all entries in bottom who's name is LIKE %query%, and also the entries for all entries in bottom who's middle's name is LIKE %query%, and also the entries for all entries in bottom who's middle's top's name is LIKE query.

I want to convert it to SQLAlchemy something like this:

 return db.session.query(Bottom).filter( or_(Bottom.name.like('%query%'),
 Bottom.middle.name.like('%query%'),
 Bottom.middle.top.name.like('%query%'))
 )).all()

In other words, if I have an entry in Bottom that is related to an entry in Middle, and that entry in Middle is related to an entry in Top, if I search Top for the appropriate name, it will return not the Middle entry that is related to it, but the Bottom entry that is related to the related Middle entry.

Here is an example:

Data:

**bottom**
id 1
name "Gus Fring"
middle_id 1
**middle**
id 1
name "Jesse Pinkman"
top_id 1
**top**
id 1
name "Walter White"

If I search for "Walter White" it will return not the Middle relation (Jesse Pinkman), but rather the Bottom relation's to the Middle relation's result (Gus Fring).

Thank you in advance :).

• manurajhada: be specific with your question.. stackoverflow.com/questions/11287909/mysql-multiple-union
• user1492385: @manurajhada what are you unclear on? What details am I not providing that would help?
• user1492385: @manurajhada I've updated it a bit, not sure if it's more clear now.

Answer 1

name_filter = '%query%'
qry = (session.query(Bottom).
 outerjoin(Middle).
 outerjoin(Top).
 filter(or_(
 Bottom.name.like(name_filter),
 Middle.name.like(name_filter),
 Top.name.like(name_filter),
 ))
 )

You will get only instances of Bottom as a result of this query execution. If you really want all 3 of them as a tuple (Bottom, Middle, Top), just replace the query(...) part to session.query(Bottom, Middle, Top).

• iChux: I love the way you answer questions: I doff my hat for you.