Nicolas Nicolas - 11 months ago 121
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",['id'),
).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:

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

Answer Source

Just add another join

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