Nicolas Nicolas - 3 months ago 36
Python Question

SQLAlchemy Build selectable query with multiple tables

I created a materialized view for my flask web application with the help of
Jeff Widman.

Unfortunately he only describe how to join two tables. I would like to create a materialized view with more than two tables.

class AnalyticV(MaterializedView):
__table__ = create_mat_view("my_view",
db.select([Table1.id.label('id'),
Table1.title.label('title'),
Table2.location.label('loc'),
Table3.time.label('time'),]
).select_from(db.join(Table1, Table2, isouter=True) )
)


How can I insert a second

select_from(db.join(Table1, Table3, isouter=True))


Table1 has two relationships to Table2 and Table3

The SQL should look like this:

SELECT Table1.id AS id,
Table1.title AS title,
Table2.location AS loc,
Table3.time AS time
FROM Table1 LEFT OUTER JOIN Table2 ON Table2.id = Table1.table2_id
LEFT OUTER JOIN Table3 ON Table3.id = Table1.table3_id

Answer

Just add another join

select_from(db.join(Table1, Table2, isouter=True).join(Table3, isouter=True))