SQLAlchemy Build selectable query with multiple tables

Question

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

Glad to know my blog post was useful!

Jeff Widman
– Jeff Widman

2017-05-10 21:27:36 +00:00
Commented May 10, 2017 at 21:27 — Jeff Widman
– Jeff Widman, Commented May 10, 2017 at 21:27
db.select() !!! Fantastic! +1

Sean McCarthy
– Sean McCarthy

2020-02-17 20:08:29 +00:00
Commented Feb 17, 2020 at 20:08 — Sean McCarthy
– Sean McCarthy, Commented Feb 17, 2020 at 20:08

r-m-n · Accepted Answer · 2016-08-27 14:04:06Z

2

Just add another join

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

answered Aug 27, 2016 at 14:04

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

N. Mauchle Over a year ago

Thanks a lot! I always tried it with db.join(Table1, Table2).

Collectives™ on Stack Overflow

SQLAlchemy Build selectable query with multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related